Purging library cache objects can be done by flushing the whole shared pool or using dbms_shared_pool.purge to purge a specific cursor. But often times, a flush with an intermediate granularity is desired so as to free up some shared pool memory with less impact to the running database. Use cases are e.g. dealing with an application that creates lots of literal cursors, or just temporarily so. It would be nice for dbms_shared_pool.purge to purge a group of cursors (1) given an object name to purge all SQLs referencing that object, or (2) given a schema name to purge all SQLs parsed by that schema. The overloaded form of the purge procedure
PROCEDURE PURGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN
OBJNAME VARCHAR2 IN
NAMESPACE NUMBER IN
HEAPS NUMBER IN
appears to do (1). But it's not working.
In Oracle 10g or lower, a DDL on an object purges all the SQLs referencing this object. In 11g or higher, a DDL on it invalidates the SQLs referencing the object but the SQLs remain in library cache, i.e., not being purged. This behavior is fine as long as dbms_shared_pool.purge can be enhanced.