Most of the examples and discussions in the links above utilize with the following syntax:
SQL> exec sys.dbms_shared_pool.purge(‘&address, &hash_value’,'c’)
What's new in 11.2:A new (overloaded) procedure in dbms_shared_pool.purge is available in 11.2 and allows to purge statements identified by thier full_hash_value of the statement. One of the advantages compared to the previous method is that the full_hash_value is a property of a given sql statement and does not depend on the memory address of the (parent) cursor. Note this has been tested in 22.214.171.124 64 bit for Linux.
Example:myapp_user_SQL> select /*MYTEST*/ sysdate from dual; -- put test SQL statement that we want to flush in the following
admin_user_SQL> select a.FULL_HASH_VALUE from V$DB_OBJECT_CACHE a where name='select /*MYTEST*/ sysdate from dual';
-- find full_hash_value to be used in the next step
-- in this example the full_hash_value is 98d0f8fcbddf4095175e36592011cc2c
admin_user_SQL> exec sys.dbms_shared_pool.purge(HASH=>'98d0f8fcbddf4095175e36592011cc2c',namespace=>0,heaps=>1)
Additional info:full_hash_value is a 128-bit MD5 hash of the sql statement. More details on full_hash_value at this link.
A few methods to find full_hash_value given different input are listed here below:
- find full_hash_value from cache, query v$db_object_cache
- select a.FULL_HASH_VALUE from V$DB_OBJECT_CACHE a where name='select /*MYTEST*/ sysdate from dual';
- find full_hash_value from hash_value
- select full_hash_value from v$db_object_cache where hash_value=538037292
- find full_hash_value from sql_id
- find hash_value from sql_id using DBMS_UTILITY.SQLID_TO_SQLHASH
- select full_hash_value from v$db_object_cache where hash_value= DBMS_UTILITY.SQLID_TO_SQLHASH('1frjqb4h13m1c');
- compute full_hash_value from SQL text
We have discussed a method to purge cursors for the library cache that uses the full_hash_value of the cursor instead of the address and hash_value which is the more common approach (and the only one documented in previous versions). This method discussed here is available in 11.2.