Purge a single sql_id from the shared pool

When you want to flush or purge just a single (or a few) statement from the Shared Pool you can use the following query (all on a single line in SQL Developer e.g.):

exec for c in (select address, hash_value from v$sqlarea where sql_id='3hab0nxcfsxyj') loop sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'C'); end loop;

-- If and only if the first argument is a cursor address and hash-value,
--        the flag parameter should be set to 'C' (or 'c').

Happy flushing!

Tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.