11g Useful Sql


  • This topic is empty.
Viewing 1 reply thread
  • 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)


Viewing 1 reply thread
  • You must be logged in to reply to this topic.