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 TYPES OF AUDITING

                                                            AUDITING Auditing is for verification purpose     Types of auditing: Privilege level Statement level Object level To enable the auditing  audit_trail=true or db or os To disable the auditing audit_trail=false or none If the value is true it will enable the auditing and audit files… 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

VI EDITOR COMMANDS

VI EDITOR COMMANDS $vi file1 Default mode will escape mode If u want switch into insert mode We have commands I : Current position insert mode A : at the end of the line insert mode a: next to the current position insert mode s:… Read More

Oracle : SPFILE AND PFILE

                                SPFILE   SPFILE IS SERVER PARAMETER FILE SPFILE IS A SEMI BINARY FILE. WE CANNOT EDIT MANUALLY WE CAN EDIT AS DYNAMICALLY BY USING ALTER SYSTEM ITS SPECIALY MADE FOR RAC (REAL APPLICATION CLUSTER) WE CAN CREATE SPFILE AT ANY STAGE(nomount,mount,open) IF YOUR DATABASE… Read More