11g Useful Sql

Home Forums DBA – Wiki 11g Useful Sql

Tagged: 

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

  • Author
    Posts
  • #1969

    Amit Bansal
    Keymaster

    In 11g, we can flush a particular sql out of shared pool (also available in 10.2.0.4 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
    Member

    Amit,

    In 10.2.0.4, 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)

    Cheers!!

You must be logged in to reply to this topic.