Get out of here!

Sometimes we need to take some cursor outside the library cache, for this we have the DBMS_SHARED_POOL.PURGE procedure, of course there are some very old tricks, like to create synonyms on one of the tables using the cursor or put comments in columns tables, but this invalidates all cursors on the table, not just one.

First we get address and hash_value in v$sqlarea view by SQL_ID

ANR> SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID =’118mxumpyfqbn’;

ADDRESS HASH_VALUE
________________ __________
0000000571707EE8 3957807476

execute the following command

ANR> exec DBMS_SHARED_POOL.PURGE (‘0000000571707EE8, 3957807476’, ‘C’);

If we perform the search in the v$sqlarea  again, this should return zero rows

ANR> SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID =’118mxumpyfqbn’;

0 rows selected

 
HTH – Antonio NAVARRO

 

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