Flushing single statement from Library cache

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

I came through an article that to flush a single sql statement from library cache as my scenario was like

a. Found  a query in v$sqlarea available for many days in one of the datwarehouse database .
b .When checked for the session, it is not available and it is not doing anything, Just check the below output.


1. select a.sql_id, a.* from v$sqlarea a where a.sql_text like 'INSERT INTO%TARIFF_TEST%'


3nfu1csjdnt9q INSERT INTO "TESTBUF". "TARIFF_TEST" (COUNTRY, TARIFF, COUNTRY_CODE) VALUES (:B1 , :B2 , :B3 ) RETURNING ROWID INTO :O0 (CLOB) INSERT INTO "TESTBUF". "TARIFF_TEST" (COUNTRY, TARIFF_030, COUNTRY_CODE) VALUES (:B1 , :B2 , :B3 ) RETURNING ROWID INTO :O0 3nfu1csjdnt9q 6673 4832 3816 0 1 0 1 0 0 1 0 1 0 1 2014-01-07/16:52:35 0 1 3 0 37 0 0 0 17457 0 0 1 2 ALL_ROWS E289FB89E126A80034011000AEF9C3E2CFFA331056414555519521105555551545545558591555449665851D5511058555555155515122555415A0EA0E5551454265455454449081566E001696A35615551403025415505AE126A800050802000002000000100000000100004000000208D00700000080350024B30A000101000030F0000400009259050000C01A00E126A8006564240202643202320000020003020A0A05050A140002000032F4010000500A0A0A0A64E803000064E04003FFFF00001008020000080032 358037407 87 87 TESTBUF 0 00000003DFECBD88 584738102 1227644037 0 SQL Developer 1012150930 0 0 0 19175 00000003DF647C68 N VALID 0 07-JAN-14 N N N 0 0 1 0 0 07-JAN-14 0 0 49152 3 49152 0 0 0 1 2 0 0

2. So, now, check for the address and hash value of the sql_id.

select address,hash_value from v$sqlarea where sql_id='3nfu1csjdnt9q'

00000003DFECBD88 584738102


3. Next, we need to purge the statement from shared pool


exec dbms_shared_pool.purge('<address>','<hash_value>',C)  -- C for cursor
exec dbms_shared_pool.purge('00000003DFECBD88,584738102','C')

 P          package/procedure/function
--          Q          sequence
--          R          trigger
--          T          type
--          JS         java source
--          JC         java class
--          JR         java resource
--          JD         java shared data
--          C          cursor


SQL> exec dbms_shared_pool.purge('00000003DFECBD88,584738102','C')

PL/SQL procedure successfully completed.


4. Now, check for the statement in v$sqlarea view.

SQL> select address,hash_value from v$sqlarea where sql_id='3nfu1csjdnt9q';

no rows selected