This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions


Purge cursors given an object or schema

User51642 Yong Huang
User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge
edited Jan 30, 2018 4:52PM in Database Ideas - Ideas

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


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.

0 votes

Active · Last Updated


  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    And what excatly is your suggested idea for improvement?

  • jgarry
    jgarry DBA/Programmer Beautiful San Marcos, CAMember Posts: 13,844 Gold Crown

    Also, remember this is a shared system, and things won't be purged while they are still in use.  When checking to see if things are in the library cache, be sure to keep that in mind.  They're invalidated for the next use.  As well, since not everything gets cleaned out right away because of this, fragmentation can happen, and being more specific about what group to purge could conceivably make that worse when the associated objects are scattered about.

    Not a bad idea, but could have complex consequences, especially with complicated dependencies.

  • User51642 Yong Huang
    User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge

    And what excatly is your suggested idea for improvement?

    The suggested improvement is making the existing overloaded purge procedure that takes schema and objname work, and either adding one more procedure that only takes schema, or allowing the existing one to make objname nullable (so effectively the purge is done to the whole schema). Alternatively, provide a new SQL command e.g. alter system flush shared_pool {for schema xxx | for object xxx}.