Query to flush the running sql in oracle database.

 This describes how to flush a running sql from shared pool using sql_id in oracle database.

Some times we have to flush the running sql due to bad sql plan. New sql plan can be picked if we can flush the running sql. Make sure the running sql has bad sql plan before flushing it.

Find the sql_id using below query.

select sid,serial#,sql_id from v$session where sid=27;

Flush the running sql from shared pool using below steps.

Step 1: 

select address, hash_value from v$sqlarea where sql_id like 'fr7q58byvjtsu';

Step 2:

exec dbms_shared_pool.purge('00000000B2E45B48, 513826068','C');

Thanks for going through the post.......