Tuesday, March 16, 2010

DBMS_SHARED_POOL.PURGE

To clear out a specific cursor from the shared pool.

SYS AS SYSDBA> alter session set events '5614566 trace name context forever';

Session altered.

SYS AS SYSDBA> select sql_text,address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea where version_count > 100;

SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
---------------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
SELECT BID, OFFER, PRICE_TIME FROM PRICE_LATEST WHERE INSTRUMENT_ID = :B1
07000001D275CE98 1404435209 1752 1 473 0
1752


SYS AS SYSDBA> exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
Enter value for address: 07000001D275CE98
Enter value for hash_value: 1404435209

PL/SQL procedure successfully completed.

SYS AS SYSDBA> select sql_text,address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea where version_count > 100;


no rows selected