My Oracle Cookbook

This is my Oracle Cookbook, with useful queries and notes, functions and packages to have a quick resolution and intervention resource.

Hope you enjoy: ^_^

 

View execution plan for a given SQL_ID

 

select * from TABLE(dbms_xplan.display_awr('SQL_ID'));

View bind variables values

 

SELECT NAME,VALUE_STRING FROM DBA_HIST_SQLBIND where sql_id='YOUR_SQL_ID';

Copy users from one database to another

 

SELECT 'CREATE USER '||USERNAME||' identified by values '''||(SELECT PASSWORD from SYS.USER$ where NAME=DBA_USERS.USERNAME)||''' default tablespace '||DEFAULT_TABLESPACE||' temporary tablespace TEMP QUOTA UNLIMITED ON '||DEFAULT_TABLESPACE||';' from DBA_USERS where ACCOUNT_STATUS='OPEN';

Find Hard Parse SQL

 

select substr(sql_text,1,50), count(*) from v$sql
group by substr(sql_text,1,50) having count(*) > 25

Check table’s foreign keys

 

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name from all_constraints where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints where constraint_type in ('P','U') and table_name='TABLE_NAME_THAT_WE_WANT_TO_CHECK');

Compare two tables row by row

 

(select * from A 
 minus
 select * from B) -- Rows in A that are not in B
union all
( 
 select * from B 
 minus
 select * from A
) -- rows in B that are not in A

Make DESCRIBE command behave properly on SQLPLUS

 

SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF

Disable Database password expiry

 

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Flashback query

 

 select  * from sys.view$ as of timestamp ...

Identify object locks

 

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

Gather Database statistics

 

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER   DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample     BOOLEAN  DEFAULT FALSE,
method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
stattab          VARCHAR2 DEFAULT NULL,
statid           VARCHAR2 DEFAULT NULL,
options          VARCHAR2 DEFAULT 'GATHER',
objlist          OUT      ObjectTab,
statown          VARCHAR2 DEFAULT NULL,
gather_sys       BOOLEAN  DEFAULT TRUE,
no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')));
set timing on;
exec DBMS_STATS.GATHER_DATABASE_STATS (degree=>24);

Find locked statistics

 

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Unlock locked statistics

 

exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');

Create Partitions

 

DECLARE
W_OLD_MONTHS VARCHAR2(6) :='201101';
W_NEW_MONTHS VARCHAR2(6) :='201102';
PROCEDURE DB_CRE_TABLE_PARTITION(P_OWNER            VARCHAR2,
P_TABLE_NAME       VARCHAR2,
P_CONSO_DAY        VARCHAR2,
P_PARTITION_PREFIX VARCHAR2,
P_TABLESPACE       VARCHAR2 DEFAULT 'DATA',
P_DEBUG_OUTPUT     PLS_INTEGER DEFAULT 0) IS
BEGIN
EXECUTE IMMEDIATE( 'alter table ' || P_OWNER||'.'||P_TABLE_NAME ||
' add partition ' || P_PARTITION_PREFIX ||
P_CONSO_DAY || ' values (''' || P_CONSO_DAY ||
''') TABLESPACE ' || P_TABLESPACE);
EXCEPTION
WHEN OTHERS THEN
IF P_DEBUG_OUTPUT = 1 THEN
DBMS_OUTPUT.PUT_LINE('Error:' || SQLERRM);
END IF;
END DB_CRE_TABLE_PARTITION;
BEGIN
FOR REC IN (SELECT T2.TABLE_OWNER,
T2.TABLE_NAME,
REPLACE(T2.PARTITION_NAME,
W_OLD_MONTHS) PARTITION_NAME,
T2.TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS T2
WHERE T2.PARTITION_NAME LIKE '%'||W_OLD_MONTHS
AND T2.TABLE_NAME NOT IN
(SELECT T3.TABLE_NAME
FROM ALL_TAB_PARTITIONS T3
WHERE T3.PARTITION_NAME LIKE '%'||W_NEW_MONTHS))
LOOP
DB_CRE_TABLE_PARTITION(REC.TABLE_OWNER,
REC.TABLE_NAME,
W_NEW_MONTHS,
REC.PARTITION_NAME,
REC.TABLESPACE_NAME);
END LOOP;
END;
/

How does one copy data from one database to another in SQL*Plus?

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example:
 

SQL> COPY FROM scott/tiger@db1 TO scott/tiger@db2 INSERT mytable USING select * from mytable;

Get object DDL

 

set long 999999999
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE', 'OBJECT_NAME', 'OWNER') from dual;

Create index with parallelism

 

CREATE UNIQUE INDEX SCHEMA.INDEX_NAME ON SCHEMA.TABLE_NAME ("FIELD") COMPUTE STATISTICS TABLESPACE "TABLESPACE_NAME" PARALLEL 48;

Manage ACLs

 

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl         => 'utl_all.xml');
COMMIT;
END;
/
begin
dbms_network_acl_admin.create_acl (
acl         => 'utl_all.xml',
description => 'Allow all',
principal   => 'SYSTEM',
is_grant    => TRUE,
privilege   => 'connect'
);
commit;
end;
/
begin
dbms_network_acl_admin.add_privilege (
acl       => 'utl_all.xml',
principal => 'JOHN',
is_grant  => TRUE,
privilege => 'connect'
);
commit;
end;
/
begin
dbms_network_acl_admin.add_privilege (
acl       => 'utl_all.xml',
principal => 'SCOTT',
is_grant  => TRUE,
privilege => 'connect'
);
commit;
end;
/
begin
dbms_network_acl_admin.assign_acl(
acl  => 'utl_all.xml',
host => 'support.oracle.com'
);
commit;
end;
/
begin
dbms_network_acl_admin.assign_acl(
acl  => 'utl_all.xml',
host => 'smtp.googlemail.com'
);
commit;
end;
/
SYS@ORARAC01> SYS@ORARAC01> SYS@ORARAC01>   2    3    4    5    6    7
ACL                            PRINCIPAL                      PRIVILE IS_GRANT             START_DATE           END_DATE
------------------------------ ------------------------------ ------- -------------------- -------------------- --------------------
/sys/acls/utl_all.xml          SYSTEM                         connect true
/sys/acls/utl_all.xml          SCOTT                            connect true
/sys/acls/utl_all.xml          JOHN                         connect true
/sys/acls/utl_all.xml          ORARAC                        connect true
/sys/acls/utl_http.xml         SCOTT                            connect true
/sys/acls/utl_http.xml         SCOTT                            resolve true
/sys/acls/utl_http.xml         ORARAC                        connect true
/sys/acls/utl_mail.xml         SCOTT                            connect true
/sys/acls/utl_mail.xml         SCOTT                            resolve true
/sys/acls/utl_mail.xml         ORARAC                        connect true
10 rows selected.

SYS@ORARAC01> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
HOST                             LOWER_PORT UPPER_PORT ACL
-------------------------------- ---------- ---------- ------------------------------
support.oracle.com                             /sys/acls/utl_all.xml
www.google.com                                             /sys/acls/utl_all.xml
smtp.googlemail.com                                   /sys/acls/utl_all.xml
localhost                                25         25 /sys/acls/utl_http.xml
smtp server host name or address                       /sys/acls/utl_mail.xml

Change AWR snapshots interval

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Get AWR report for a given SQL_ID

 

@?/rdbms/admin/awrsqrpt.sql

Get SQL Tunning Advisor report for a given SQL_ID

 

@?/rdbms/admin/sqltrpt.sql

V$SQL_MONITOR view

 

set lines 200
COL USERNAME FORMAT a15
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

 
Queries monitored for the entire current day:

select SID,SESSION_SERIAL# "SERIAL#",SQL_ID,
USERNAME,PROGRAM,SQL_EXEC_START,ELAPSED_TIME,
SQL_TEXT from V$SQL_MONITOR 
WHERE SQL_EXEC_START>TRUNC(SYSDATE) 
ORDER BY SQL_EXEC_START DESC;

Queries monitored for the last hour:

select SID,SESSION_SERIAL# "SERIAL#",SQL_ID,
USERNAME,PROGRAM,SQL_EXEC_START,ELAPSED_TIME,
SQL_TEXT from V$SQL_MONITOR 
WHERE SQL_EXEC_START>SYSDATE-(1/24) 
ORDER BY SQL_EXEC_START DESC;

Queries monitored for the fifteen minutes:

select SID,SESSION_SERIAL# "SERIAL#",SQL_ID,
USERNAME,PROGRAM,SQL_EXEC_START,ELAPSED_TIME,
SQL_TEXT from V$SQL_MONITOR 
WHERE SQL_EXEC_START>(15/1440) 
ORDER BY SQL_EXEC_START DESC;

 

Get various sessions information

 

set lines 237
col SID format 9999999
col SERIAL# format 9999999
col SPID format a12
col SQL_ID format a13
col USERNAME format a12
col MACHINE format a16
col PROGRAM format a40
col EVENT format a32
col P1TEXT format a26
col P3TEXT format a26
col WAIT_CLASS format a13
col SECONDS_IN_WAIT format 9999
col SQL_FULLTEXT format a230

select SES.SID,SES.SERIAL#,p.spid,SES.SQL_ID,SES.USERNAME,SES.MACHINE,SES.PROGRAM,WA.EVENT,WA.P1TEXT,WA.P3TEXT,WA.WAIT_CLASS,WA.SECONDS_IN_WAIT,SES.LAST_CALL_ET "Seconds running",SQL.SQL_FULLTEXT
from  V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
where SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
and    SES.paddr    = p.addr
/

Get various sessions information for a given system PID

 

set lines 237
col SID format 9999999
col SERIAL# format 9999999
col SPID format a12
col SQL_ID format a13
col USERNAME format a12
col MACHINE format a16
col PROGRAM format a40
col EVENT format a32
col P1TEXT format a26
col P3TEXT format a26
col WAIT_CLASS format a13
col SECONDS_IN_WAIT format 9999
col SQL_FULLTEXT format a230

select SES.SID,SES.SERIAL#,p.spid,SES.SQL_ID,SES.USERNAME,SES.MACHINE,SES.PROGRAM,WA.EVENT,WA.P1TEXT,WA.P3TEXT,WA.WAIT_CLASS,WA.SECONDS_IN_WAIT,SES.LAST_CALL_ET "Seconds running",SQL.SQL_FULLTEXT
from  V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
where p.SPID=&syspid
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
and    SES.paddr    = p.addr
/

RMAN backup without catalog

 

run {
allocate channel t1 type disk;
backup AS COMPRESSED backupset filesperset 16
format '/u03/app/oracle/backup/%d_t%t_s%s_p%p'
(database include current controlfile) plus archivelog;
release channel t1;
}
rman target / nocatalog
CONFIGURE DEVICE TYPE DISK PARALLELISM 48;
backup as compressed backupset archivelog all format '/u03/app/oracle/backup/%d_t%t_s%s_p%p' delete input;

Exclude all oracle system users

 

where OWNER not in ('OWBSYS','OLAPSYS','CTXSYS','EXFSYS','ORDSYS','ORDDATA','XDB','SYS', 'SYSMAN', 'SYSTEM', 'MDSYS','DMSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN');

Get Oracle Hidden Parameters and values

 

select
ksppinm,
ksppstvl
from
x$ksppi a,
x$ksppsv b
where
a.indx=b.indx and
substr(ksppinm,1,1) = '_';

Get the SID (Session ID) for a given OS PID

 

select s.sid, s.serial#, s.username,
to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
s.program clientprogram, s.module, s.machine, s.osuser,
s.status, s.last_call_et
from  v$session s, v$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sid
/

Tablespace Usage

 

select  a.TABLESPACE_NAME,
a.MBYTES Mbytes_used,
b.MBYTES Mbytes_free,
b.largest,
round(((a.MBYTES-b.MBYTES)/a.MBYTES)*100,2) percent_used
from
(
select  TABLESPACE_NAME,
sum(BYTES/1024/1024) MBYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES/1024/1024) MBYTES ,
max(BYTES/1024/1024) largest
from    dba_free_space
group   by TABLESPACE_NAME
)
b
where   a.TABLESPACE_NAME=b.TABLESPACE_NAME
order   by ((a.MBYTES-b.MBYTES)/a.MBYTES) desc
/

Convert a BLOB column to a VARCHAR2 column

 

create or replace function F(B BLOB)
return clob is
c clob;
n number;
begin
if (b is null) then
return null;
end if;
if (length(b)=0) then
return empty_clob();
end if;
dbms_lob.createtemporary(c,true);
n:=1;
while (n+32767<=length(b)) loop
dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
n:=n+32767;
end loop;
dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
return c;
end;
/

 

2 Comments

Leave a Reply

You must be logged in to post a comment.