SQL Tips

Rather than posting loads of stuff about SQL I will  just append to here anything I find interesting.


   
  • Show Oracle hidden parameters - 
SELECT    ksppinm,    ksppstvl    FROM    x$ksppi a,    x$ksppsv b
    WHERE    a.indx=b.indx    AND    ksppinm like '_allow%'
  
SQL> /


KSPPINM         KSPPSTVL
-------------------------------------------------- ----------------------------------------
_allow_error_simulation       FALSE
_allow_resetlogs_corruption      FALSE
_allow_terminal_recovery_corruption     FALSE
_allow_read_only_corruption      FALSE
_allow_file_1_offline_error_1245     FALSE
.... etc.


connect as another user through system
    SQL> alter user DW_REP grant connect through system;
    User altered.

    SQL> exit

    [oracle@u ~]$ sqlplus system/password

    SQL*Plus: Release 12.2.0.1.0 Production on Fri May 10 04:55:41 2019
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    Last Successful login time: Thu May 09 2019 12:33:09 -04:00
    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    SQL> connect system[DW_REP]/password
    Connected.
    SQL> show user
    USER is "DW_REP"

      Ever needed to output to Excel

    SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;

    SPOOL sample.xls;

    SELECT * FROM EMP;


    SPOOL OFF;

    • I would love to know
    Simple example, say you have a 5 column table, called TEST_TAB

    so to view 4 columns excluding col2

    select col1, col3, col4, col5 from test_tab;

    Would love to know whether Oracle has ever considered a "negative" select, not sure how it 
    would work in reality, so :

    select -col2 from test_tab; (would product the same result as the sql above but less work)

    Thoughts ?

    • listagg
    Not something I have ever used until today – 'listagg' came in with 11g.

    Handy for grouping when something belongs to multiple groups.

    e.g. 

    select * from customer;

    SQL> /


    CUSTOMER_ID TYPE_ID
    ----------- -------
    12345 1
    56789 2
    12345 2
    98765 3
    12345 3


    select customer_id, listagg(type_id, ',') within group (order by type_id) as type_id from customer group by customer_id

    SQL> /

    CUSTOMER_ID TYPE_ID
    ----------- ------------------------------
    12345 1,2,3
    56789 2
    98765 3


    • Ampersands in SQL Character Fields
    There are several ways to handle inserting character strings containing ampersands from
    SQL*PLUS in order to avoid a prompt for input.

    insert into test_table ('Salt &Pepper');

    enter value for Pepper:

    1 - Precede the insert statement with 'set define off'. This stops interpretation of all special characters.

    2 - Change the define character to something other than the ampersand - 'set define #'.

    3 - Use 'set scan off' which stops scanning for input.

    4 - Set an escape character to remove the special meaning from the ampersand:

    set escape insert into test_table ('Salt \& Pepper'); 1 row inserted

    5 - When inserting from embedded SQL or when using any method where you cannot use the 'set'       functionality of SQL*PLUS, you can use the ASCII equivalent of the ampersand:

    insert into test_table ('Salt '||chr(38)||' Pepper');

    Other handy ASCII equivalents are chr(39) for the single quote and chr(10) for the line feed.

    • Quick script to view datafile info
    SELECT
    a.tablespace_name,
    a.file_name,
    a.bytes/1024/1024 allocated_MBB,
    b.free_MB
    FROM
    dba_data_files a,
    (SELECT file_id, SUM(bytes)/1024/1024 free_MB
    FROM dba_free_space b GROUP BY file_id) b
    WHERE
    a.file_id=b.file_id
    ORDER BY
    a.tablespace_name;

    • Example of handling dates in Oracle

    SQL> desc datetest
    Name                                              Null?    Type
    --------------------------------------------- -------- -------------------
    WARRANT                                                  VARCHAR2(20)
    STARTDATE                                               DATE
    FINISHDATE                                               DATE

    SQL>  alter session set nls_date_format = 'DD-MON-YYYY'; -- specify how you want date format


    (or from UNIX o/s level  - 
    NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT)

    SQL> select * from datetest where to_date(startdate,'dd-mon-yyyy') < to_date('21-FEB-2017');

    SQL> insert into datetest(warrant, startdate, finishdate) values ('A12345','22-JUN-2015','22-DEC-2019');

    1 row created.

    SQL> commit
      2  /

    Commit complete.

    SQL> select * from datetest where to_date(startdate,'dd-mon-yyyy') < to_date('31-DEC-2015');

    A12345               22-JUN-2015 22-DEC-2019

    expanding on this to select dates prior to a certain date in a procedure.

    SQL> select * from datetest
      2  /

    WARRANT        STARTDATE FINISHDATE
    -------------------- --------- ---------
    Y12345               22-JUN-15 22-DEC-19
    A12345               22-DEC-17 24-DEC-17
    X56789               10-JAN-16 11-FEB-16
    Z12345               10-JAN-17 15-FEB-17

    SQL> exec warrantlist;

    Warrant : X56789
    Warrant date: 10-JAN-16
    Warrant : Y12345
    Warrant date: 22-JUN-15

    PL/SQL procedure successfully completed.

    SQL> !cat warrantlst.sql

    create or replace procedure warrantlist
        is
        warrant_num varchar2(10);
        warrant_date date;

    CURSOR d1 IS SELECT distinct warrant from datetest;

    CURSOR d2 IS SELECT  max(startdate) from datetest where warrant =  warrant_num  and 
    startdate < to_date('21-FEB-2016');

    BEGIN
        open d1;
        LOOP
        FETCH d1 into warrant_num;
      EXIT when d1% NOTFOUND;

        dbms_output.put_line('Warrant : '|| warrant_num);

       open d2;

       LOOP
        FETCH d2 into warrant_date;
       EXIT when d2% NOTFOUND;

        dbms_output.put_line('Warrant date: '|| warrant_date);


      END LOOP;

     CLOSE d2;

      END LOOP;

    CLOSE d1;

    EXCEPTION
       WHEN OTHERS THEN
          raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

     END warrantlist;


    Another bit of plsql using loops within loops to show number of columns in the tables for all user with MAIN in the schema name.

    set serveroutput on;
    set lines 200

    DECLARE

    v_app_user dba_users.username%TYPE;
    v_object_name dba_objects.object_name%TYPE;
    v_obj_name2 dba_objects.object_name%TYPE;

    v_num number;

    CURSOR a1 IS select username from dba_users where username like '%MAIN' order by 1;

    CURSOR d1 IS select object_name from dba_objects where object_type ='TABLE' and owner = v_app_user order by 1;

    cursor d2 is select v_object_name, count(a.table_name) from dual, dba_tab_columns a where a.table_name = v_object_name and owner = v_app_user;

    BEGIN
    open a1;
        LOOP
           FETCH a1 into v_app_user;
           EXIT when a1% NOTFOUND;
        open d1;
        LOOP
                 FETCH d1 into v_object_name;
                 EXIT when d1% NOTFOUND;

        open d2;
        LOOP
                FETCH d2 into v_obj_name2, v_num;
                EXIT when d2% NOTFOUND;
                dbms_output.put_line(rpad(v_app_user,30) || rpad(v_obj_name2,40) || '       Columns : ' || rpad(v_num,9));

        END LOOP;
        CLOSE d2;
    END LOOP;
    close d1;
    END LOOP;
    close a1;
    END;
    /


    Variation to count rows rather columns in the table


    DECLARE
    v_app_user dba_users.username%TYPE;
    v_object_name dba_objects.object_name%TYPE;
    vnum number;
    CURSOR a1 IS select username from dba_users where username like '%MAIN' order by 1;
    CURSOR d1 IS select object_name from dba_objects where object_type ='TABLE' and owner = v_app_user order by 1;
    BEGIN
    open a1;
        LOOP
           FETCH a1 into v_app_user;
           EXIT when a1% NOTFOUND;
        open d1;
        LOOP
                 FETCH d1 into v_object_name;
                 EXECUTE IMMEDIATE 'select count(*) from ' || v_app_user ||'.'|| v_object_name into vnum;
                 dbms_output.put_line(rpad(v_app_user,30)||'  '||rpad(v_object_name,30)||' Rows: '|| rpad(vnum,9));
                 EXIT when d1% NOTFOUND;
        END LOOP;
    close d1;
    END LOOP;
    close a1;
    END;

    • join, minus, intersect - quick examples very simple
      SQL> select * from a;

      COUNTRY_CODE COUNTRY
      ------------ ----------------------------------------
      1 England
      2 Scotland
      3 Wales
      4 Ireland

      SQL> select * from b;

      COUNTRY_CODE COUNTRY
      ------------ ----------------------------------------
      3 Wales
      4 Ireland
      5 France
      6 Italy

      SQL> select a.country, b.country from a,b where a.country_code(+) = b.country_code
      2/

      SQL> col country format a20
      SQL> /

      COUNTRY COUNTRY
      -------------------- --------------------
      Wales            Wales
      Ireland          Ireland
      Italy
      France

      SQL> select country from a minus select country from b;

      COUNTRY
      --------------------
      England
      Scotland

      SQL> select country from a intersect select country from b;

      COUNTRY
      --------------------
      Ireland
      Wales

      SQL> select country from b minus select country from a;

      COUNTRY
      --------------------
      France
      Italy

      SQL> select a.country, b.country from a,b where a.country_code(+) = b.country_code
      2 /

      COUNTRY COUNTRY
      -------------------- --------------------
      Wales Wales
      Ireland Ireland
      Italy
      France

      SQL> select a.country from a inner join b on a.country_code = b.country_code
      2 /

      COUNTRY
      --------------------
      Wales
      Ireland


      SQL> select distinct country from (select country from a union all select country from b) x order by country;

      COUNTRY
      --------------------
      England
      France
      Ireland
      Italy
      Scotland
      Wales


      Create two procedures and a function - create a package (very quick and easy example).

        
      create or replace package test as
      function getlengthWidth(len number, wid number) return number;
      procedure hello1;
      procedure hello2;
      end test;


      create or replace package body test as 
      function getlengthWidth(len number, wid number) return number as
      varea number;
      begin
      varea:= len * wid;
      return varea;
      end getlengthWidth;
      procedure hello1 is 
      begin
      dbms_output.put_line('Hello1');
      end hello1;
      procedure hello2 is
      begin
      dbms_output.put_line('Hello2');
      end hello2;
      end test;


      SQL> select text from dba_source where name = 'HELLO1';


      procedure hello1

      is

      begin

      dbms_output.put_line('Hello1');

      end;

      SQL> select text from dba_source where name = 'HELLO2';


      procedure hello2

      is

      begin

      dbms_output.put_line('Hello2');
      end;

      select test.getlengthWidth(15,45) from dual;

      675

      SQL> set serveroutput on;

      SQL> execute test.hello1;
      Hello1
      PL/SQL procedure successfully completed.

      -- Create a function



      SQL> create or replace function area


      2 (len in number,

      3 wid in number)


      4 return number

      5 as

      6 varea number;

      7 begin

      8 varea:= len * wid;

      9 return varea;

      10 end;
      11 /

      Function created.

      SQL> select area (10,30) area from dual;

      AREA
      ----------
      300

      Procedure to create a table - how much use not sure!!

      create or replace procedure testtable
      is
      begin
      var1:='create table testable (testnum number)';
         EXECUTE IMMEDIATE var1;
      dbms_output.put_line('Table testable created!!!');
      end testable;


      ---------------------------

      srvctl stop / start database


      [oracle@ora01 ~]$ srvctl stop database -d mbtpay
      [oracle@ora01 ~]$ srvctl start database -d mbtpay 

      --------------------------------

      SYS_PRIVS --

      select * from dba_sys_privs where grantee = 'TEST';

      e.g. 
      GRANTEE PRIVILEGE ADM
      ------------------------------ ---------------------------------------- ---
      TEST UNLIMITED TABLESPACE NO

      1 row selected.

      SQL> select * from dba_role_privs where grantee = 'TEST'; 

      ------------------------



      Created some dba roles and need to grant to a user



      e.g.



      select 'grant '|| role || ' to AUSER;' from dba_roles where role like '%MAIN%'



      'GRANT'||ROLE||'TO AUSER;'
      ---------------------------------------------------------
      grant CNG_MAIN_READONLY to AUSER;
      grant NEXTFARE_MAIN_READONLY to AUSER;
      grant NIS_MAIN_READONLY to AUSER;



      -----------------------------

      This is handy..


      --rebuild any indexes or index partitions for the current user
      set serveroutput on
      declare
        procedure ddl (str in varchar2) is
        begin
          execute immediate (str);
          dbms_output.put_line (str);
        end ddl;
      begin
        for t in (select table_name from user_tables order by table_name) loop  
          for i in ( 
              select index_name, partition_name, 'partition' ddl_type
              from user_ind_partitions
              where (index_name) in 
                 ( select index_name
                   from   user_indexes
                   where  table_name  = t.table_name
                 )
              and status = 'UNUSABLE'
              union all
              select index_name, subpartition_name, 'subpartition' ddl_type
              from user_ind_subpartitions
              where (index_name) in 
                 ( select index_name
                   from   user_indexes
                   where  table_name  = t.table_name
                 )
              and status = 'UNUSABLE'
              union all
              select index_name, null, null
              from user_indexes
              where table_name  = t.table_name
              and status = 'UNUSABLE'
          )
          loop
            if i.ddl_type is null then
              ddl('alter index '||i.index_name||' rebuild');
            else
              ddl('alter index '||i.index_name||' rebuild '||i.ddl_type||' '||i.partition_name);
            end if;
          end loop;
        end loop;
      end;
      /


      Ever wondered how to find out when the oracle database tablespace or data file was created ?
      The following data dictionary synonym can give you that information:




      select tablespace_name,name,creation_time from V$DATAFILE_HEADER;

      • Test SYS user password 

      This works "sqlplus sys/wrongpassword as sysdba" so does "sqlplus sys/rightpassword as sysdba"; in fact any password works.

      So to test use "sqlplus sys/wrongpassword@oradb as sysdba" 

      SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 13 06:24:51 2019
      Copyright (c) 1982, 2014, Oracle. All rights reserved.
      ERROR:
      ORA-01017: invalid username/password; logon denied❌

      [oracle@db01 ~]$ sqlplus sys/rightpasword@oradb1 as sysdba
      SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 13 06:29:40 2019
      Copyright (c) 1982, 2014, Oracle. All rights reserved.

      Connected to:
      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
      and Real Application Testing options
      SQL> show user;
      USER is "SYS" ✔



      Why have I put this here - because I always forget to use the @database connection string and can never remember how to check the sys password; lazy dba using "as sysdba" so normally never need to know the sys password - except when I do!



      Explained here :








      • Creation time of an Oracle View
       select o.object_name,o.created,o.last_ddl_time
        from dba_objects o
       where o.object_name like 'SERVICENOW%' and o.object_type='VIEW'

      SQL> /
      SERVICENOW_KPI_V                                                                    22-MAY-19 04-OCT-19
      SERVICENOW_KPI_V                                                                    26-SEP-19 30-SEP-19


      • DDL for a constraint
        1* SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner) FROM   dba_constraints where CONSTRAINT_NAME = 'CC1_TXN_CHANNEL_MAP'
      SQL> /




      Need to drop & recreate if you add a value

      SQL> alter table EDW.TXN_CHANNEL_MAP drop constraint CC1_TXN_CHANNEL_MAP
        2  /
      Table altered.


      SQL> alter table EDW.TXN_CHANNEL_MAP ADD CONSTRAINT CC1_TXN_CHANNEL_MAP CHECK (TXN_SOURCE IN ('SalesOrder','SaleTxn','UseTxn','RefundOrder','PatronOrder')) ENABLE
        2  /
      Table altered.

      • Test connection script


      ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
      ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1; export ORACLE_HOME
      PATH=$PATH:$ORACLE_HOME/bin; export PATH

      a=1
      while [ $a -lt 20 ]
      do

      sqlplus -silent system/racattack@collabn-cluster-scan:1521/stockton.racattack << EOF
      set echo off
      set heading off
      set feedback off


      select host_name from gv\$instance where instance_number=userenv('instance');

      --SELECT UTL_INADDR.get_host_address from dual;
      exit;
      EOF
      #   if [ $a -eq 5 ]
      #   then
      #      break
      #   fi
         a=`expr $a + 1`
         sleep 1
      done


      • Putting a RAC cluster quorum disk online
      ALTER DISKGROUP NFPS_VOTE_DATA ONLINE QUORUM DISK NFPS_VOTE_DATA_B007; 

      Check using :

      set lines 300
      set pages 66
      col asmdisk format a20
      col failgroup format a20
      col site_name format a10
      col path format a40
      SELECT
        dg.group_number,
        dg.name AS diskgroup,
        d.name AS asmdisk,
        d.total_mb,
        d.state,
        d.mount_status,
        d.failgroup,
        d.site_name,
        d.header_status,
        d.path
      FROM V$ASM_DISKGROUP dg left join V$ASM_DISK d on dg.group_number = d.group_number
      ORDER BY 1,2,3  



      Show SQL Plus variable value

      set serveroutput on;
      set lines 200;
      define russo='SYSTEM';
      select * from dba_data_files where tablespace_name =  '&russo';
      define russo

      DDL for DB LINKS

      SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0
      SET LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

      BEGIN
         DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
         DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
      END;
      /

      SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner)
      FROM   dba_db_links
      WHERE  owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'));

      SET PAGESIZE 14 LINESIZE 1000 FEEDBACK ON VERIFY ON



      Connect as a different user
      This maybe be handy when you know say the system password but don't know an application password.

      For example to connect as DW_REP : 

      SQL> alter user DW_REP grant connect through system; 

      SQL> connect system[DW_REP]/password
      Connected.

      SQL> show user
      USER is "DW_REP"

      When finished revoke priv.

      ALTER USER DW_REP REVOKE CONNECT THROUGH system;


      Backup a package e.g.

      select decode( type||'-'||to_char(line,'fm99999'), 'PACKAGE BODY-1', '/'||chr(10), null) || decode(line,1,'create or replace ', '' ) || text text from user_source
      where name = upper('frm_extract_sale_txn_payment')

      Wanted to change the oggadmin user password to oggadmin for a test box - not really a good idea.
      alter profile default limit password_verify_function null;
      SQL> alter user oggadmin identified by oggadmin;
      
      User altered.
      

      Dropping non default users (19c)

      SQL> select 'drop user ' || username || ' cascade;' from dba_users
      where username
      not in (select username from dba_users where oracle_maintained = 'Y')

       Where oracle_maintained column is not available Oracle < 11 use this to see non oracle users :

      "select username, profile from dba_users where trunc(created) > (select trunc(created) from dba_users where username='SYS') order by 1"
       
      To exclude oracle_maintained users in 11g: 

      select username from dba_users
      where username NOT IN (select name from system.logstdby$skip_support)
      order by 1;


      To list oracle_maintained users in 11g:
       
      select username from dba_users
      where username IN (select name from system.logstdby$skip_support)
      order by 1;

      Use this to see what a Unix process is doing.

      set lines 200
      COLUMN   Client_User FORMAT   a10
      COLUMN   DB_User FORMAT   a10
      COLUMN   MACHINE FORMAT   a30
      SELECT s.STATUS "Status",
      s.TYPE "Type",
      s.username "DB_User",
      s.osuser "Client_User",
      s.server "Server",
      s.machine "Machine",
      s.module "Module",
      s.logon_time "Connect Time",
      s.process "Process",
      p.spid,
      p.pid,
      s.SID,
      s.audsid,
      SYSDATE - (s.last_call_et / 86400) "Last_Call"
      FROM v$session s,
      v$process p
      WHERE s.paddr = p.addr(+)
      AND spid = &1

      Space Usage :

      SELECT m.tablespace_name,
          round(max(m.tablespace_size*t.block_size/1024/1024),2) TOL_METRICS,
          round(sum(d.maxbytes/1024/1024),2) MAX_SIZE,
          round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,2) TOL_ALLOC,
          round(max(m.used_space*t.block_size/1024/1024),2) USED,
          round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),2) PERC_ALLOC,
          round(max(m.used_percent),2) PERC_MAX, --PERC_METRICS
          round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,2) FREE_ALLOC,
          round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREE_MAX --FREE METRIC
        FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d--, dba_thresholds tt
        WHERE m.tablespace_name=t.tablespace_name
        AND d.tablespace_name=t.tablespace_name
        --and tt.metrics_name='Tablespace Space Usage'
        --and tt.object_name is null
        GROUP BY m.tablespace_name
        order by 7 desc;

      Space Usage with pdbs (adjust as approp.)
       
      set lines 200
      col PDB_NAME format a20
      set pages 66
      break on PDB_NAME

      SELECT m.tablespace_name, n.name "PDB_NAME",
                  round(max(m.tablespace_size*t.block_size/1024/1024),2) TOL_METRICS,
                  round(sum(d.maxbytes/1024/1024),2) MAX_SIZE,
                  round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,2) TOL_ALLOC,
                  round(max(m.used_space*t.block_size/1024/1024),2) USED,
                 round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),2) PERC_ALLOC,
                  round(max(m.used_percent),2) PERC_MAX, --PERC_METRICS
                  round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,2) FREE_ALLOC,
                  round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREE_MAX --FREE METRIC
              FROM  cdb_tablespace_usage_metrics m, v$pdbs n, cdb_tablespaces t, cdb_data_files d--, cdb_thresholds tt
             WHERE (m.tablespace_name=t.tablespace_name)
              and (m.con_id=n.con_id)
              AND d.tablespace_name=t.tablespace_name
              --and tt.metrics_name='Tablespace Space Usage'
              --and tt.object_name is null
              GROUP BY m.tablespace_name, n.name
            order by 2,7 desc

      Gather schema stats - 

      SQL> exec dbms_stats.gather_schema_stats('testschema',estimate_percent=>100,degree=>8);

      Just an example as I always forget syntax.


      No comments:

      Post a Comment