Flushing a Single SQL Statement out of the Object Library Cache

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

MANUAL
Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address
of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the
concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘7yc%’;

ADDRESS HASH_VALUE
—————- ———-
000000085FD77CF0 808321886

SQL> exec DBMS_SHARED_POOL.PURGE (‘000000085FD77CF0, 808321886’, ‘C’);

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘7yc%’;

no rows selected

SCRIPT

DECLARE
  name varchar2(50);
  version varchar2(3);
BEGIN
  select regexp_replace(version,'..*') into version from v$instance;

  if version = '10' then
    execute immediate
      q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport
  end if;

  select address||','||hash_value into name
  from v$sqlarea
  where sql_id like '&sql_id';

  sys.dbms_shared_pool.purge(name,'C',1);

END;
/

https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single

http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/

http://www.oracle-guy.com/scripts/flush_sql.sql

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s