Category: SQL scripts

set lines 150 pages 2000 accept v_sqlid prompt ‘Enter SQL_ID : ‘ select * from table(dbms_xplan.display_cursor(‘&v_sqlid’, 0));

Good Docs. (1) http://www.oracle-training.cc/oracle_tips_hidden_parameters.htm (2) FOR EAAPPPROD22 (UOCQA) ====================== Also I did some research on ‘cache buffer chains’ CBC events and found that wait on UOCQA instance ( on eaappprod22 ) was occurring due to wait on specific chain, not because of wait on specific Block or ROW. As per current configuration, # of linked-list…

Read More “cache buffer chain latch” »

dbaltxprod50->WEBP18F@(/home/oracle)/oracle/app/oracle/product/10.2/OPatch/opatch lsinventory Invoking OPatch 10.2.0.2.0 Oracle interim Patch Installer version 10.2.0.2.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /oracle/app/oracle/product/10.2 Central Inventory : /var/opt/oracle/oraInventory from : /oracle/app/oracle/product/10.2/oraInst.loc OPatch version : 10.2.0.2.0 OUI version : 10.2.0.2.0 OUI location : /oracle/app/oracle/product/10.2/oui Log file location : /oracle/app/oracle/product/10.2/cfgtoollogs/opatch/opatch-2006_Aug_28_17-52-47-E DT_Mon.log Lsinventory Output file location : /oracle/app/oracle/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory-2006_Aug_28 _17-52-47-EDT_Mon.txt…

Read More “Finding Oracle Patches with opatch” »

18:02:01 PVM@AWRTEST:-> select * from pvm1; NO ——— 100 20 40 1 2 200 24 7 rows selected. Elapsed: 00:00:00.01 18:02:17 PVM@AWRTEST:-> select no from ( select no, row_number() over (order by no desc) r from pvm1) where r=2; NO ——— 100 1 row selected. Elapsed: 00:00:00.02 18:02:38 PVM@AWRTEST:-> select no from ( select no,…

Read More “Order by with ROWNUM” »

In this article I will describe the steps used to identify which shared memory and semaphore segments are owned by a particular instance in Oracle V7, V8.0, V8i, and V9i. This can be helpful in recovery situations where the database instance has not released its shared memory and semaphores on database shutdown. Consider the situation…

Read More “oradebug ipcrm ipcs” »

Locktree.sql is renamed with $ORACLE_HOME/rdbms/admin/utllockt.sql This will show the lock hierarchy in Oracle.

Test data

1998/10 In PL/SQL you can suspend a session using dbms_lock.sleep (seconds) which is equivalent to the ‘sleep’ command in UNIX. The parameter (number data type) can even be specified in hundredth of a second (10, 10.01, 10.5, 0.99 are all legal values). 1998/11 When you TRUNCATE a table, the storage parameter NEXT is automatically reset…

Read More “oracle tips… from http://www.bijoos.com/oracle/douknow.htm” »

column name format a60 column created format a25 select name, to_char(creation_time, ‘DD-MON-RRRR:HH24:MI’) created from v$datafile_header order by creation_time /

Find hash value & time when this plan was taken for particular sql id. Filename : fphv.sql REM *** Find Plan Hash value column plan_hash_value format 999999999999 accept v_sqlid prompt ‘Enter SQL_ID : ‘ select distinct sql_id, plan_hash_value, to_char(timestamp, ‘DD-MON-RRRR:HH24:MI:SS’) from dba_hist_sql_plan where sql_id=’&v_sqlid’; Then, Apply sql id and plan_hash_value for a unique plan. REM…

Read More “To find explain plan for a statement that occurred in past.” »