Finding locked objects
select object_id from dba_objects where object_name = ‘MYTABLE’; select sid, id1, id2 from v$lock where id1= &objid or id2 = &objid;
Category: SQL scripts
Changing Instance Name ( No DB_NAME)
Note Instance name : represented by $ORACLE_SID database name : represented by DB_NAME init.ora parameter. You can change instance name to any name with following requirements: Here we are chaning instance name from AWRTEST1 to PARESH. Exising setup: ORACLE_SID=AWRTEST1 Step1: $ export ORACLE_SID=PARESH; Step2: Create password file for new instance PARESH. $ cd $ORACLE_HOME/dbs $…
Find nth max and min.
First, the query for Max: SELECT * FROM TAB1 a WHERE &N = (SELECT count(DISTINCT(b.col1)) FROM TAB1 b WHERE a.col1=b.col1) If N=1 will return first max or first min. N=2 will return second max or min.
DBMS_Shared_pool pinning triggers
How to pin a stored procedure/functions ? 1.You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the ‘P’ flag, which is the default value (so you can leave it out). Triggers are pinned with ‘R’ and anonymous plsql blocks need any letter other than [p,P,r,R] as…
TOP-N Sql to find Nth max or Top N rows
PURPOSE This note explains the TOP-N Query, a new feature (as of 8.1.5) that allows users to query on ROWNUM and order results with ORDER BY. This functionality is similar to the offerings of other database software such as the “Top Values’ property option in Microsoft Access. Top-N Query to Solve ORDER BY…ROWNUM Inaccuracy: ==================================================…
Facts about SCN and Rollback Segment
1. When should you create more rollback segments? The number of rollback segments needed to prevent contention between processes can be determined with the help of the monitor rollback display and with the use of the v$waitstat table. The rollback monitor column “header waits/sec” gives an indication of the current rollback segment contention. Waits are…
logminer and my_lbu
dbms_logmnr.add_logfile(‘/export/home/oracle/paresh/arch1_558809894_83113.arc’, options => dbms_logmnr.new); dbms_logmnr.add_logfile(‘/export/home/oracle/paresh/arch1_558809894_83114.arc’, options => dbms_logmnr.addfile); dbms_logmnr.add_logfile(‘/export/home/oracle/paresh/arch1_558809894_83115.arc’, options => dbms_logmnr.addfile); dbms_logmnr.add_logfile(‘/export/home/oracle/paresh/arch1_558809894_83116.arc’, options => dbms_logmnr.addfile); execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog); Then rename v$logmnr_contents to logminer_bak. Then run my_lbu.sql
How to Use DBMS_STATS to Move Statistics to a Different Database
Subject: How to Use DBMS_STATS to Move Statistics to a Different Database Doc ID: Note:117203.1 Type: FAQ Last Revision Date: 01-MAR-2007 Status: PUBLISHED To provide Oracle Support Services with statistics for debugging, please refer to the following article: Note 242489.1 Transferring Optimizer Statistics to Support Purpose: ======== The purpose of this article is to explain…
Read More “How to Use DBMS_STATS to Move Statistics to a Different Database” »
export import with parameter file.
Export with parameter file and import with same. =============================================== Task : I am exporting some tables from CS and importing thsoe tables into BASKET schema. /**************************************************** FILE NAME: exp_wo_data.par *******************/ userid=cs file=exp_bskt_15MAR07.dmp compress=N grants=N log=bskt_exp_15MAR07.log ROWS=N triggers=N statistics=NONE tables=(BSKT_AGENT_BASKET_CALL_DETAIL , BSKT_AGENT_ORDER_NOTE , BSKT_BASKET_ITEM_TYPE , BSKT_DISCOUNT_APPLY_TYPE , BSKT_DISCOUNT_REASON , BSKT_ITEM_CALC_PRICING , BSKT_ITEM_PRICING_XREF , BSKT_ITEM_SUB_PRODUCT , BSKT_PAYMENT_DETAIL…
Another Tuning Article for subheap of shared pool
http://www.mail-archive.com/oracle-l@fatcity.com/msg79721.html Oh yeah, I forgot to say that in 9.2.0.3 the shared pool was broken up into “heaps” (Oracle terminology) but whatever fancy stuff they were trying to accomplish by doing this (I think part of the magical self-tuning initiative) was buggy and this hastened the fatal 4031 situation. There is a parameter (so I…
Read More “Another Tuning Article for subheap of shared pool” »