How to flush a cursor from the shared pool

If you want to remove any cursor in the shared pool. You can do it easily via sys.DBMS_SHARED_POOL .PURGE procedure.

1. Get the address and hash_value of the cursor
SQL> SELECT sql_id, address, hash_value FROM v$sqlarea where sql_text = 'SELECT count(*) FROM t1';

SQL_ID        ADDRESS          HASH_VALUE
------------- ---------------- ----------
8mx79bxy262t8 00000000A6B3F538 2082671400

2. Purge the cursor from the shared pool by passing the above values
SQL> exec sys.DBMS_SHARED_POOL.purge('00000000A6B3F538,2082671400', 'C');

PL/SQL yordam² ba■ar²yla tamamland².

3. Check if the cursor is still in the shared pool or not
SQL> SELECT count(*) FROM v$sqlarea where sql_text = 'SELECT count(*) FROM t1';

  COUNT(*)
----------
         0

As it can be seen from the above example, it has been flushed from the shared pool. You can also use this procedure for the other database objects.

--'P' or 'p' is stands for procedure
--'T' or 't' is stands for type
--'R' or 'r' is stands for trigger
--'Q' or 'q' is stands for sequence

exec sys.DBMS_SHARED_POOL.purge('USER_SCHEMA_NAME.PROCEDURE_NAME', 'P');

exec sys.DBMS_SHARED_POOL.purge('USER_SCHEMA_NAME.TYPE_NAME', 'T');

exec sys.DBMS_SHARED_POOL.purge('USER_SCHEMA_NAME.TRIGGER_NAME', 'R');

exec sys.DBMS_SHARED_POOL.purge('USER_SCHEMA_NAME.SEQUENCE_NAME', 'Q');

I hope this helps 🙂

Posted in DBA
Leave a Reply

Your email address will not be published.