[Amit Bansal]

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;

[Saurabh Sood]

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!!

[Author]

Posts