Category: SQL scripts

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.” »

set lines 120 pages 200 column event format a30 column object_type format a14 column object_name format a30 column owner format a10 column sql_text format a120 spool currwaitobj accept event_name prompt ‘Enter fraction of event to trace : ‘ accept start_time1 prompt ‘Start Time in (DD-MON-RRRR:HH24:MI format) : ‘ accept stop_time1 prompt ‘Stop Time in (DD-MON-RRRR:HH24:MI…

Read More “currwaitobj.sql SQl_ID and SQL statement you can get from currwaitobj.sql” »

REM fplan.sql REM Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE. column plan_hash_value format 999999999999 accept v_sqlid prompt ‘Enter SQL_ID : ‘ accept v_plan_hash_value prompt ‘Enter PLAN Hash Value : ‘ SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM dba_hist_sql_plan START WITH id =…

Read More “Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE fplan.sql” »