How-to flush a SQL out library cache in 11g

In 11g, DDL on a table will not flush SQL out of the library cache. Oracle introduced procedure DBMS_SHARED_POOL.PURGE to do that :

SQL> select ADDRESS, HASH_VALUE from V$SQL where hash_value = 499912407;

ADDRESS          HASH_VALUE
---------------- ----------
000000091C659200  499912407

SQL> exec DBMS_SHARED_POOL.PURGE ('000000091C659200, 499912407', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQL where hash_value = 499912407;

no rows selected

For more information, reference:
1. http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_shared_pool.htm
2. How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package [ID 457309.1]

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How-to flush a SQL out library cache in 11g

  1. Pingback: Confluence: Productie

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

%d bloggers like this: