How to Purge a Single SQL Statement from Shared Pool

<<Back to PT Main Page

Purging a Single SQL Statement from Shared Pool

There are some cases where you want to flush a single SQL statement from shared pool. You can do this using  DBMS_SHARED_POOL.PURGE package

SQL> select ADDRESS, HASH_VALUE,sql_text from V$SQLAREA where sql_text like '%select * from %'
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ----------------------------------------------------------------------
000000009BC59A78 1215902625 select * from test.test

SQL> exec DBMS_SHARED_POOL.PURGE ('000000009BC59A78,1215902625','A');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE,sql_text from V$SQLAREA where HASH_VALUE='1215902625';
no rows selected