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 ON SET PAUSE ‘Press Return to Continue’ SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND substr(ksppinm,1,1)… 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 132 set pagesize 1000 set echo on col rbs format a6 col oracle_user format a12 heading “Oracle|Username” col… 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

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

Oracle : USER MANAGEMENT

USER MANAGEMENT How to create user? Syntax sys>> create user <username> identified by <password>; Create user u1 identified by u1; How to assign the privileges to user? Grant connect,resource to u1; How to assign the tablespace to user? Alter user u1 default tablespace userdata; How… Read More

Oracle : TABLESPACE MANAGEMENT

TABLESPACE MANAGEMENT WHAT IS TABLESPACE ? IT’S A LOGICAL LINK BETWEEN USER AND DATAFILES.IT IS LOGICAL STORAGE OF DATA   DICTIONARY: EXTENTS INFORMATION WILL GET FROM BASE TABLES LOCAL: EXTENTS INFORMATION WILL GET FROM DATAFILE HEADERS TABLESPACE MANAGEMENT   HOW TO CREATE THE TABLESPACE SYS>>… Read More