11g Useful Sql


This topic contains 1 reply, has 0 voices, and was last updated by  Amit Bansal 6 years, 8 months ago.

  • Author
  • #1969

    Amit Bansal

    In 11g, we can flush a particular sql out of shared pool (also available in by applying patch)

    select ‘exec dbms_shared_pool.purge(”’||ADDRESS||’,’||HASH_VALUE||”’,”C”)’ from V$SQLAREA where SQL_ID=’&sqlid’;

    In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

    ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

    ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’ immediate;

  • #2155

    Saurabh Sood


    In, if we do not want to apply patch, we can do this by setting event 5614566 at session level and then using dbms_shared_pool.purge (may have to run dbmspool.sql if purge is not present)


You must be logged in to reply to this topic.