DBMS_SHARED_POOL.PURGE Not Working? Here’s the Fix

The DBMS_SHARED_POOL.PURGE procedure is commonly used to remove specific cursors from the SQL area, but in many cases, it doesn’t work as expected. Let’s explore why this happens and how to resolve it effectively.

When DBMS_SHARED_POOL.PURGE Fails

Consider the following example where an SQL cursor cannot be cleared using the procedure.

SQL> SELECT address, hash_value FROM v$sqlarea WHERE sql_id = '9x12bcdlmnxyz';

ADDRESS          HASH_VALUE
---------------- ----------
08000200AABB3EF1 3412768934

Since we now have the ADDRESS and HASH_VALUE, let’s attempt to purge it using the following statement:

SQL> EXEC DBMS_SHARED_POOL.PURGE('08000200AABB3EF1,3412768934','C');

PL/SQL procedure successfully completed.

Now, let’s check if the cursor cache has been removed:

SQL> SELECT address, hash_value FROM v$sqlarea WHERE sql_id = '9x12bcdlmnxyz';

ADDRESS          HASH_VALUE
---------------- ----------
08000200AABB3EF1 3412768934

Even after executing the purge command, the cursor cache is still present. If the cursor is currently in use by another session, the procedure should ideally return an error instead of a misleading success message.

The Alternative: Flushing the Shared Pool

If DBMS_SHARED_POOL.PURGE does not work, a more effective approach is to flush the entire shared pool. This can be done using the following command:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

After running this command, check if the cursor cache has been cleared:

SQL> SELECT address, hash_value FROM v$sqlarea WHERE sql_id = '9x12bcdlmnxyz';

no rows selected

This confirms that the cursor cache has been successfully removed.

Conclusion

While DBMS_SHARED_POOL.PURGE can be useful for clearing specific cursors, it may not always work if the cursor is actively being used. In such cases, using ALTER SYSTEM FLUSH SHARED_POOL is a more reliable solution for freeing up memory in the shared pool.