{"id":18,"date":"2011-03-04T19:35:04","date_gmt":"2011-03-04T19:35:04","guid":{"rendered":"http:\/\/selectsysdatefromdual.gnusystems.net\/?page_id=18"},"modified":"2014-07-04T09:10:04","modified_gmt":"2014-07-04T09:10:04","slug":"my-oracle-cookbok","status":"publish","type":"page","link":"https:\/\/selectsysdatefromdual.gnusystems.net\/?page_id=18","title":{"rendered":"My Oracle Cookbook"},"content":{"rendered":"<p>This is my Oracle Cookbook, with useful queries and notes, functions and packages to have a quick resolution and intervention resource.<\/p>\n<p>Hope you enjoy: ^_^<br \/>\n<br \/>\n[toc]<br \/>\n<\/p>\n<h3>View execution plan for a given SQL_ID<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nselect * from TABLE(dbms_xplan.display_awr('SQL_ID'));\r\n<\/pre>\n<p><\/p>\n<h3>View bind variables values<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nSELECT NAME,VALUE_STRING FROM DBA_HIST_SQLBIND where sql_id='YOUR_SQL_ID';\r\n<\/pre>\n<p><\/p>\n<h3>Copy users from one database to another<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nSELECT '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';\r\n<\/pre>\n<p><\/p>\n<h3>Find Hard Parse SQL<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nselect substr(sql_text,1,50), count(*) from v$sql\r\ngroup by substr(sql_text,1,50) having count(*) > 25\r\n<\/pre>\n<p><\/p>\n<h3>Check table&#8217;s foreign keys<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nselect 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');\r\n<\/pre>\n<p><\/p>\n<h3>Compare two tables row by row<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\n(select * from A \r\n minus\r\n select * from B) -- Rows in A that are not in B\r\nunion all\r\n( \r\n select * from B \r\n minus\r\n select * from A\r\n) -- rows in B that are not in A\r\n<\/pre>\n<p><\/p>\n<h3>Make DESCRIBE command behave properly on SQLPLUS<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nSET LINESIZE 80\r\nSET DESCRIBE DEPTH 2\r\nSET DESCRIBE INDENT ON\r\nSET DESCRIBE LINE OFF\r\n<\/pre>\n<p><\/p>\n<h3>Disable Database password expiry<\/h3>\n<p>&nbsp;<\/p>\n<pre>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;<\/pre>\n<h3>Flashback query<\/h3>\n<p>&nbsp;<\/p>\n<pre> select  * from sys.view$ as of timestamp ...<\/pre>\n<h3>Identify object locks<\/h3>\n<p>&nbsp;<\/p>\n<pre>select l1.sid, ' IS BLOCKING ', l2.sid\r\nfrom v$lock l1, v$lock l2\r\nwhere l1.block =1 and l2.request &gt; 0\r\nand l1.id1=l2.id1\r\nand l1.id2=l2.id2;<\/pre>\n<h3>Gather Database statistics<\/h3>\n<p>&nbsp;<\/p>\n<pre>DBMS_STATS.GATHER_DATABASE_STATS (\r\nestimate_percent NUMBER   DEFAULT to_estimate_percent_type\r\n(get_param('ESTIMATE_PERCENT')),\r\nblock_sample     BOOLEAN  DEFAULT FALSE,\r\nmethod_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),\r\ndegree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),\r\ngranularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),\r\ncascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),\r\nstattab          VARCHAR2 DEFAULT NULL,\r\nstatid           VARCHAR2 DEFAULT NULL,\r\noptions          VARCHAR2 DEFAULT 'GATHER',\r\nobjlist          OUT      ObjectTab,\r\nstatown          VARCHAR2 DEFAULT NULL,\r\ngather_sys       BOOLEAN  DEFAULT TRUE,\r\nno_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (\r\nget_param('NO_INVALIDATE')));\r\nset timing on;\r\nexec DBMS_STATS.GATHER_DATABASE_STATS (degree=&gt;24);<\/pre>\n<h3>Find locked statistics<\/h3>\n<p>&nbsp;<\/p>\n<pre>select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;<\/pre>\n<h3>Unlock locked statistics<\/h3>\n<p>&nbsp;<\/p>\n<pre>exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');<\/pre>\n<h3>Create Partitions<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nDECLARE\r\nW_OLD_MONTHS VARCHAR2(6) :='201101';\r\nW_NEW_MONTHS VARCHAR2(6) :='201102';\r\nPROCEDURE DB_CRE_TABLE_PARTITION(P_OWNER            VARCHAR2,\r\nP_TABLE_NAME       VARCHAR2,\r\nP_CONSO_DAY        VARCHAR2,\r\nP_PARTITION_PREFIX VARCHAR2,\r\nP_TABLESPACE       VARCHAR2 DEFAULT 'DATA',\r\nP_DEBUG_OUTPUT     PLS_INTEGER DEFAULT 0) IS\r\nBEGIN\r\nEXECUTE IMMEDIATE( 'alter table ' || P_OWNER||'.'||P_TABLE_NAME ||\r\n' add partition ' || P_PARTITION_PREFIX ||\r\nP_CONSO_DAY || ' values (''' || P_CONSO_DAY ||\r\n''') TABLESPACE ' || P_TABLESPACE);\r\nEXCEPTION\r\nWHEN OTHERS THEN\r\nIF P_DEBUG_OUTPUT = 1 THEN\r\nDBMS_OUTPUT.PUT_LINE('Error:' || SQLERRM);\r\nEND IF;\r\nEND DB_CRE_TABLE_PARTITION;\r\nBEGIN\r\nFOR REC IN (SELECT T2.TABLE_OWNER,\r\nT2.TABLE_NAME,\r\nREPLACE(T2.PARTITION_NAME,\r\nW_OLD_MONTHS) PARTITION_NAME,\r\nT2.TABLESPACE_NAME\r\nFROM ALL_TAB_PARTITIONS T2\r\nWHERE T2.PARTITION_NAME LIKE '%'||W_OLD_MONTHS\r\nAND T2.TABLE_NAME NOT IN\r\n(SELECT T3.TABLE_NAME\r\nFROM ALL_TAB_PARTITIONS T3\r\nWHERE T3.PARTITION_NAME LIKE '%'||W_NEW_MONTHS))\r\nLOOP\r\nDB_CRE_TABLE_PARTITION(REC.TABLE_OWNER,\r\nREC.TABLE_NAME,\r\nW_NEW_MONTHS,\r\nREC.PARTITION_NAME,\r\nREC.TABLESPACE_NAME);\r\nEND LOOP;\r\nEND;\r\n\/<\/pre>\n<h3>How does one copy data from one database to another in SQL*Plus?<\/h3>\n<p>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:<br \/>\n&nbsp;<\/p>\n<pre>\r\nSQL&gt; COPY FROM scott\/tiger@db1 TO scott\/tiger@db2 INSERT mytable USING select * from mytable;<\/pre>\n<h3>Get object DDL<\/h3>\n<p>&nbsp;<\/p>\n<pre>set long 999999999\r\nSELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE', 'OBJECT_NAME', 'OWNER') from dual;<\/pre>\n<h3>Create index with parallelism<\/h3>\n<p>&nbsp;<\/p>\n<pre>CREATE UNIQUE INDEX SCHEMA.INDEX_NAME ON SCHEMA.TABLE_NAME (\"FIELD\") COMPUTE STATISTICS TABLESPACE \"TABLESPACE_NAME\" PARALLEL 48;<\/pre>\n<h3>Manage ACLs<\/h3>\n<p>&nbsp;<\/p>\n<pre>BEGIN\r\nDBMS_NETWORK_ACL_ADMIN.drop_acl (\r\nacl         =&gt; 'utl_all.xml');\r\nCOMMIT;\r\nEND;\r\n\/\r\nbegin\r\ndbms_network_acl_admin.create_acl (\r\nacl         =&gt; 'utl_all.xml',\r\ndescription =&gt; 'Allow all',\r\nprincipal   =&gt; 'SYSTEM',\r\nis_grant    =&gt; TRUE,\r\nprivilege   =&gt; 'connect'\r\n);\r\ncommit;\r\nend;\r\n\/\r\nbegin\r\ndbms_network_acl_admin.add_privilege (\r\nacl       =&gt; 'utl_all.xml',\r\nprincipal =&gt; 'JOHN',\r\nis_grant  =&gt; TRUE,\r\nprivilege =&gt; 'connect'\r\n);\r\ncommit;\r\nend;\r\n\/\r\nbegin\r\ndbms_network_acl_admin.add_privilege (\r\nacl       =&gt; 'utl_all.xml',\r\nprincipal =&gt; 'SCOTT',\r\nis_grant  =&gt; TRUE,\r\nprivilege =&gt; 'connect'\r\n);\r\ncommit;\r\nend;\r\n\/\r\nbegin\r\ndbms_network_acl_admin.assign_acl(\r\nacl  =&gt; 'utl_all.xml',\r\nhost =&gt; 'support.oracle.com'\r\n);\r\ncommit;\r\nend;\r\n\/\r\nbegin\r\ndbms_network_acl_admin.assign_acl(\r\nacl  =&gt; 'utl_all.xml',\r\nhost =&gt; 'smtp.googlemail.com'\r\n);\r\ncommit;\r\nend;\r\n\/\r\nSYS@ORARAC01&gt; SYS@ORARAC01&gt; SYS@ORARAC01&gt;   2    3    4    5    6    7\r\nACL                            PRINCIPAL                      PRIVILE IS_GRANT             START_DATE           END_DATE\r\n------------------------------ ------------------------------ ------- -------------------- -------------------- --------------------\r\n\/sys\/acls\/utl_all.xml          SYSTEM                         connect true\r\n\/sys\/acls\/utl_all.xml          SCOTT                            connect true\r\n\/sys\/acls\/utl_all.xml          JOHN                         connect true\r\n\/sys\/acls\/utl_all.xml          ORARAC                        connect true\r\n\/sys\/acls\/utl_http.xml         SCOTT                            connect true\r\n\/sys\/acls\/utl_http.xml         SCOTT                            resolve true\r\n\/sys\/acls\/utl_http.xml         ORARAC                        connect true\r\n\/sys\/acls\/utl_mail.xml         SCOTT                            connect true\r\n\/sys\/acls\/utl_mail.xml         SCOTT                            resolve true\r\n\/sys\/acls\/utl_mail.xml         ORARAC                        connect true\r\n10 rows selected.\r\n\r\nSYS@ORARAC01&gt; SELECT host, lower_port, upper_port, acl FROM dba_network_acls;\r\nHOST                             LOWER_PORT UPPER_PORT ACL\r\n-------------------------------- ---------- ---------- ------------------------------\r\nsupport.oracle.com                             \/sys\/acls\/utl_all.xml\r\nwww.google.com                                             \/sys\/acls\/utl_all.xml\r\nsmtp.googlemail.com                                   \/sys\/acls\/utl_all.xml\r\nlocalhost                                25         25 \/sys\/acls\/utl_http.xml\r\nsmtp server host name or address                       \/sys\/acls\/utl_mail.xml<\/pre>\n<h3>Change AWR snapshots interval<\/h3>\n<pre>\r\nBEGIN\r\n  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(\r\n    interval  => 30);          -- Minutes. Current value retained if NULL.\r\nEND;\r\n\/\r\n<\/pre>\n<h3>Get AWR report for a given SQL_ID<\/h3>\n<p>&nbsp;<\/p>\n<pre>@?\/rdbms\/admin\/awrsqrpt.sql<\/pre>\n<h3>Get SQL Tunning Advisor report for a given SQL_ID<\/h3>\n<p>&nbsp;<\/p>\n<pre>@?\/rdbms\/admin\/sqltrpt.sql<\/pre>\n<h3>V$SQL_MONITOR view<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nset lines 200\r\nCOL USERNAME FORMAT a15\r\nalter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';\r\n<\/pre>\n<p>&nbsp;<br \/>\nQueries monitored for the entire current day:<\/p>\n<pre>\r\nselect SID,SESSION_SERIAL# \"SERIAL#\",SQL_ID,\r\nUSERNAME,PROGRAM,SQL_EXEC_START,ELAPSED_TIME,\r\nSQL_TEXT from V$SQL_MONITOR \r\nWHERE SQL_EXEC_START>TRUNC(SYSDATE) \r\nORDER BY SQL_EXEC_START DESC;\r\n<\/pre>\n<p>Queries monitored for the last hour:<\/p>\n<pre>\r\nselect SID,SESSION_SERIAL# \"SERIAL#\",SQL_ID,\r\nUSERNAME,PROGRAM,SQL_EXEC_START,ELAPSED_TIME,\r\nSQL_TEXT from V$SQL_MONITOR \r\nWHERE SQL_EXEC_START>SYSDATE-(1\/24) \r\nORDER BY SQL_EXEC_START DESC;\r\n<\/pre>\n<p>Queries monitored for the fifteen minutes:<\/p>\n<pre>\r\nselect SID,SESSION_SERIAL# \"SERIAL#\",SQL_ID,\r\nUSERNAME,PROGRAM,SQL_EXEC_START,ELAPSED_TIME,\r\nSQL_TEXT from V$SQL_MONITOR \r\nWHERE SQL_EXEC_START>(15\/1440) \r\nORDER BY SQL_EXEC_START DESC;\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h3>Get various sessions information<\/h3>\n<p>&nbsp;<\/p>\n<pre>set lines 237\r\ncol SID format 9999999\r\ncol SERIAL# format 9999999\r\ncol SPID format a12\r\ncol SQL_ID format a13\r\ncol USERNAME format a12\r\ncol MACHINE format a16\r\ncol PROGRAM format a40\r\ncol EVENT format a32\r\ncol P1TEXT format a26\r\ncol P3TEXT format a26\r\ncol WAIT_CLASS format a13\r\ncol SECONDS_IN_WAIT format 9999\r\ncol SQL_FULLTEXT format a230\r\n\r\nselect 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\r\nfrom  V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P\r\nwhere SES.STATUS='ACTIVE'\r\nAND SES.SQL_ID=SQL.SQL_ID\r\nAND SES.SID=WA.SID\r\nand    SES.paddr    = p.addr\r\n\/\r\n\r\n<\/pre>\n<h3>Get various sessions information for a given system PID<\/h3>\n<p>&nbsp;<\/p>\n<pre>set lines 237\r\ncol SID format 9999999\r\ncol SERIAL# format 9999999\r\ncol SPID format a12\r\ncol SQL_ID format a13\r\ncol USERNAME format a12\r\ncol MACHINE format a16\r\ncol PROGRAM format a40\r\ncol EVENT format a32\r\ncol P1TEXT format a26\r\ncol P3TEXT format a26\r\ncol WAIT_CLASS format a13\r\ncol SECONDS_IN_WAIT format 9999\r\ncol SQL_FULLTEXT format a230\r\n\r\nselect 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\r\nfrom  V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P\r\nwhere p.SPID=&syspid\r\nAND SES.SQL_ID=SQL.SQL_ID\r\nAND SES.SID=WA.SID\r\nand    SES.paddr    = p.addr\r\n\/\r\n\r\n<\/pre>\n<h3>RMAN backup without catalog<\/h3>\n<p>&nbsp;<\/p>\n<pre>run {\r\nallocate channel t1 type disk;\r\nbackup AS COMPRESSED backupset filesperset 16\r\nformat '\/u03\/app\/oracle\/backup\/%d_t%t_s%s_p%p'\r\n(database include current controlfile) plus archivelog;\r\nrelease channel t1;\r\n}\r\nrman target \/ nocatalog\r\nCONFIGURE DEVICE TYPE DISK PARALLELISM 48;\r\nbackup as compressed backupset archivelog all format '\/u03\/app\/oracle\/backup\/%d_t%t_s%s_p%p' delete input;<\/pre>\n<h3>Exclude all oracle system users<\/h3>\n<p>&nbsp;<\/p>\n<pre>where OWNER not in ('OWBSYS','OLAPSYS','CTXSYS','EXFSYS','ORDSYS','ORDDATA','XDB','SYS', 'SYSMAN', 'SYSTEM', 'MDSYS','DMSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN');<\/pre>\n<h3>Get Oracle Hidden Parameters and values<\/h3>\n<p>&nbsp;<\/p>\n<pre>select\r\nksppinm,\r\nksppstvl\r\nfrom\r\nx$ksppi a,\r\nx$ksppsv b\r\nwhere\r\na.indx=b.indx and\r\nsubstr(ksppinm,1,1) = '_';<\/pre>\n<h3>Get the SID (Session ID) for a given OS PID<\/h3>\n<p>&nbsp;<\/p>\n<pre>select s.sid, s.serial#, s.username,\r\nto_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,\r\np.pid oraclepid, p.spid \"ServerPID\", s.process \"ClientPID\",\r\ns.program clientprogram, s.module, s.machine, s.osuser,\r\ns.status, s.last_call_et\r\nfrom  v$session s, v$process p\r\nwhere p.spid=nvl('&amp;unix_process',' ')\r\nand s.paddr=p.addr\r\norder by s.sid\r\n\/<\/pre>\n<h3>Tablespace Usage<\/h3>\n<p>&nbsp;<\/p>\n<pre>select  a.TABLESPACE_NAME,\r\na.MBYTES Mbytes_used,\r\nb.MBYTES Mbytes_free,\r\nb.largest,\r\nround(((a.MBYTES-b.MBYTES)\/a.MBYTES)*100,2) percent_used\r\nfrom\r\n(\r\nselect  TABLESPACE_NAME,\r\nsum(BYTES\/1024\/1024) MBYTES\r\nfrom    dba_data_files\r\ngroup   by TABLESPACE_NAME\r\n)\r\na,\r\n(\r\nselect  TABLESPACE_NAME,\r\nsum(BYTES\/1024\/1024) MBYTES ,\r\nmax(BYTES\/1024\/1024) largest\r\nfrom    dba_free_space\r\ngroup   by TABLESPACE_NAME\r\n)\r\nb\r\nwhere   a.TABLESPACE_NAME=b.TABLESPACE_NAME\r\norder   by ((a.MBYTES-b.MBYTES)\/a.MBYTES) desc\r\n\/<\/pre>\n<h3>Convert a BLOB column to a VARCHAR2 column<\/h3>\n<p>&nbsp;<\/p>\n<pre>create or replace function F(B BLOB)\r\nreturn clob is\r\nc clob;\r\nn number;\r\nbegin\r\nif (b is null) then\r\nreturn null;\r\nend if;\r\nif (length(b)=0) then\r\nreturn empty_clob();\r\nend if;\r\ndbms_lob.createtemporary(c,true);\r\nn:=1;\r\nwhile (n+32767&lt;=length(b)) loop\r\ndbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));\r\nn:=n+32767;\r\nend loop;\r\ndbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));\r\nreturn c;\r\nend;\r\n\/<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &nbsp; select * from TABLE(dbms_xplan.display_awr(&#8216;SQL_ID&#8217;)); View bind variables values &nbsp; SELECT NAME,VALUE_STRING FROM DBA_HIST_SQLBIND where sql_id=&#8217;YOUR_SQL_ID&#8217;; Copy users from one database to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":[],"_links":{"self":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages\/18"}],"collection":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18"}],"version-history":[{"count":52,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages\/18\/revisions"}],"predecessor-version":[{"id":25,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages\/18\/revisions\/25"}],"wp:attachment":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}