Durham Oracle: 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

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

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;

Looking back at the above it is pretty terrible - a better approach :

DECLARE

vnum number;

CURSOR a1 IS select owner,table_name from dba_tables where owner in (select username from dba_users where username like 'ENG%') order by 2,1;

BEGIN

for a_row in a1

loop

EXECUTE IMMEDIATE 'select /*+ PARALLEL */ count(*) from ' || a_row.owner ||'.'|| a_row.table_name into vnum;

dbms_output.put_line(rpad(a_row.owner,30)||' '||rpad(a_row.table_name,30)||' Rows: '|| rpad(vnum,9));

end loop;

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

begin

dbms_output.put_line('Hello1');

end;

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

procedure hello2

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 :

https://www.thegeekdiary.com/why-can-i-login-as-sysdba-with-any-username-and-password/

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

Space Usage on say v11 database -

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999


select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

Gather schema stats -

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

Just an example as I always forget syntax.

Durham Oracle

SQL Tips

No comments:

Post a Comment