Different ways to invalidate a cursor in memory for Oracle database

Purpose : Whilst you may have a various different situation and need to invalidate a cursor from the oracle cache/memory at the instance level, I always had the need after pinning a SQL profile.

Note : Pinning a plan through SQL profile doesn't call for an explicit hard parsing.

Simple method/way to invalidate a cursor

1. Flush the SHARED_POOL

Eg.
alter system flush shared_pool;

2. Delete or Gather statistics of the related objects (table/index) which are referenced in the cursor (execution plan) using DBMS_STATS

3. Grant or Revoke privilege on the related objects (table) which are referenced in the cursor

Eg.
I always use the following commands together on the tables which are referenced in the SQL query/cursor

grant select on scott.emp to system;
revoke select on scott.emp from system;

4. Alter the related objects (table/index) which are referenced in the cursor using DDL command.

5. You can also PURGE the cursor from the shared pool

Eg.
exec dbms_shared_pool.purge(',','C');

The above methods helped me in the situation which I described in the PURPOSE section.

However the below alternate methods may also be considered depending on your situation.

6. You can even add a hint to the SQL query to create a new sql_id. Hence it will be a fresh parse.

Eg.
select /* query1 */ empno, ename from emp;
select /* query2 */ empno, ename from emp;


7. Ofcourse, always database BOUNCE will never let you down :-) Just an extreme step.

Keywords : parse, hard, soft, invalid cursor, invalidate cursor, memory, cache, oracle