Flush A Single SQL Statement
In 11g, the DBMS_SHARED_POOL package has a couple of new procedures. One of them is the PURGE procedure which allows individual objects to be flushed from the shared pool. The create statement for this package can be found in the $ORACLE_HOME/rdbms/admin/dbmspool.sql script. I first ran across this about a year ago on Fairlie Rego’s Blog. The modification history shows that the PURGE procedure was written back in Aug, 2006 – so it’s been around for a while. The PURGE procedure can be used to dump objects from the shared pool including code (procedure/package/function), sequences, type definitions, triggers, and cursors (i.e. SQL statments). The ability to flush a single SQL statement is what caught my eye. Unfortunately, the option to purge cursors is not in the 11.1 documentation but it is documented (lightly) in the header of the package definition as you can see here.
