pvmehta.com

First statement before you start processing?? SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS1; OR dbms_transaction.use_rollback_segment(‘BIG_RBS1’);

column last_number format 9999999999999999 select * from user_sequences where sequence_name in (‘UOC_ORDER_SEQ_ID’); select 2400000000 – CurrVAl – 20 LAST_NUMBER from dual; alter sequence UOC_ORDER_SEQ_ID increment by NOCACHE; Increment Once. select UOC_ORDER_SEQ_ID.nextval from dual; Confirm Value comes to 2300000000. After that alter sequence UOC_ORDER_SEQ_ID increment by 1 NOCACHE; alter sequence UOC_ORDER_SEQ_ID maxvalue 2499999999 NOCACHE;

Subject: Upgrading from 8.1.X to 9.X – Btree Bitmap Plan Issues – Diagnosing and Resolving Doc ID: Note:259126.1 Type: TROUBLESHOOTING Last Revision Date: 13-MAR-2006 Status: PUBLISHED PURPOSE The Oracle Cost Based Optimizer is continually been enhanced. These enhancements were designed to improve performance but in some cases can cause a minority of queries to perform…

Read More “_B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade” »

TNS-00512 starting second listener in same node Hi all, I have a 8.1.7.4 listener running OK with a 9.2.0.1 listener in the same Solaris box, I get TNS-00512 starting second listener from 9.2.0.1 ORACLE_HOME. I have changed the ports number, but the problem still persist !!! Here are the listener.ora file: Listener.ora (8.1.7.4) LISTENER =…

Read More “Multiple listeners” »

RAC Notes Note:241114.1 Step-By-Step Installation of RAC on Linux – Single Node (Oracle9i 9.2.0 with OCFS) Linux OCFS – Best Practices Step-by-Step Installation of RAC on Linux @ Setting up Real Application Cluster (RAC) environment on Linux – Single node Setting up Oracle Parallel Server environment on Linux – Single node @ 9iRAC: OS Port-Specific…

Read More “Good RAC & Standby Notes” »

select object_id from dba_objects where object_name = ‘MYTABLE’; select sid, id1, id2 from v$lock where id1= &objid or id2 = &objid;

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 $…

Read More “Changing Instance Name ( No DB_NAME)” »

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.

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…

Read More “DBMS_Shared_pool pinning triggers” »

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: ==================================================…

Read More “TOP-N Sql to find Nth max or Top N rows” »