DDL Wait Option in Oracle.

DDL Wait Option in Oracle. A DBA is trying to alter the table called SALES to add a column, TAX_CODE. he issues the following SQL statement:

But instead of getting something like “Table altered”, he gets:

The error message says it all: the… Read More

Oracle : Cloning user in Database

 

Oracle : Setting up glogin.sql

Setting up glogin.sql : cd $ORACLE_HOME/sqlplus/admin cp glogin.sql glogin.sql.bkp vi glogin.sql SET termout off DEFINE myv = ‘Not connected’ COLUMN myc new_value myv select user || ‘@QA@’ || global_name myc from global_name; SET sqlprompt ‘&myv:SQL> ‘ Execution : $ sqlplus scott/PW SQL*Plus: Release 12.1.0.2.0 Production… Read More

Oracle : How to Purge CURSORS related to a single SQL_ID from library cache

********************************************************************* How to Purge CURSORS related to a single SQL_ID from library cache ********************************************************************* SQL> select ADDRESS, HASH_VALUE from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; select ADDRESS, HASH_VALUE,plan_hash_value from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; SQL> exec DBMS_SHARED_POOL.PURGE (‘000000180DC67320,552744047′,’C’);  PL/SQL procedure successfully completed.  SQL> select ADDRESS, HASH_VALUE… Read More

Oracle : List all the hidden parameter and their values – &&&&&& – Simulate ORA error in Alert log

************************************************************List all the hidden parameter and their values************************************************************SET PAUSE ONSET PAUSE ‘Press Return to Continue’SET PAGESIZE 60SET LINESIZE 300 COLUMN ksppinm FORMAT A50COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvlFROM x$ksppi a, x$ksppsv bWHERE a.indx=b.indx AND substr(ksppinm,1,1) = ‘_’ORDER BY ksppinm;   *********************************Simulate ORA error in… Read More

Oracle : HOW TO TRACE SESSIONS

Oracle : HOW TO TRACE SESSIONS ==========================================================For TRACING your own session , use the following query to find details on your own session:==========================================================set linesize 132set pagesize 1000set echo oncol rbs format a6col oracle_user format a12 heading “Oracle|Username”col sid format 99999 heading “Oracle|SID”col serial# format 99999… Read More

Oracle : Delete duplicate records from table

Delete duplicate records from table Dear Readers, In this article we will see ”Delete duplicate records from table” Script if one column having duplicate records:

Script if two columns having duplicate records:

Deleting duplicate records involving multiple columns :

Thank you for… Read More