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: ^_^
[toc]
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; /
Nice 🙂 Keep it up,
Wissem
Very good…