invalidate cursor in shared pool

Sometimes it’s necessary to invalidate a specific cursor in shared pool.

In the past you had to flush the complete shared pool – which caues ALL sql statements were flushed 😦

In the package dbms_shared_pool you can flush a SINGLE cursor.

You need to identify the address and hash_value.

The following query helps you to find them:

set lines 120
col sql_text format a80
col outline_category format a10
col sql_profile format a15
col sql_patch format a10
col sql_baseline format a15
col first_load_time format a20

select substr(sql_text,1,80) sql_text, SQL_ID, ADDRESS, HASH_VALUE, 
       PLAN_HASH_VALUE, OUTLINE_CATEGORY, SQL_PROFILE, SQL_PATCH, 
       SQL_PLAN_BASELINE, EXECUTIONS, VERSION_COUNT, 
       INVALIDATIONS, parse_calls,
       FIRST_LOAD_TIME, LOADS, IS_BIND_SENSITIVE, IS_BIND_AWARE
 from v$sqlarea
  where lower (sql_text) like '%emp2%'
    and PARSING_SCHEMA_NAME = 'SCOTT'
/

now you can invalidate

exec dbms_shared_pool.purge ('<address> <hash_value>','C');

e.g.

exec dbms_shared_pool.purge('C00000004D53BD38 1537836760','C');

the statement is now removed from the shared pool….

With this package you can also purge (instead of C mentioned above)

• ‘P’ or ‘p’ to fully specify that the input is the name of a package/procedure/function.
• ‘T’ or ‘t’ to specify that the input is the name of a type.
• ‘R’ or ‘r’ to specify that the input is the name of a trigger.
• ‘Q’ or ‘q’ to specify that the input is the name of a sequence.

see more about the dbms_shared_pool-package