Purging a SQL plan from shared pool. "Purging a Single cursor from shared pool"

This Document is applicable for Oracle 11g Release 2.
======================================

Identify the problemetic sql_id.

Find the Address and hash_value of the sql_id

In my example sql_id is 'arxk2v5bw36mp'

col OWNER format a10
col NAMESPACE format a10
col TYPE format a10

select d.OWNER,d.NAMESPACE,d.TYPE,d.SHARABLE_MEM,d.KEPT,d.FULL_HASH_VALUE,
s.sql_id,s.address,s.hash_value
from v$db_object_cache d,v$sqlarea s
where d.name=s.SQL_TEXT
and s.sql_id='&sql_id'

OWNER      NAMESPACE  TYPE       SHARABLE_MEM KEP FULL_HASH_VALUE                  SQL_ID        ADDRESS          HASH_VALUE
---------- ---------- ---------- ------------ --- -------------------------------- ------------- ---------------- ----------
           SQL AREA   CURSOR            12616 NO  7541aaf7b87e0b3babf642d957c19a75 arxk2v5bw36mp C0000006B0B15310 1472305781
           SQL AREA   CURSOR                0 NO  7541aaf7b87e0b3babf642d957c19a75 arxk2v5bw36mp C0000006B0B15310 1472305781
           SQL AREA   CURSOR             4720 NO  7541aaf7b87e0b3babf642d957c19a75 arxk2v5bw36mp C0000006B0B15310 1472305781


Flush the SQL olan form library cache.
========================================
exec dbms_shared_pool.purge('C0000006B0B15310,1472305781','C',65);

select d.OWNER,d.NAMESPACE,d.TYPE,d.SHARABLE_MEM,d.KEPT,d.FULL_HASH_VALUE,
s.sql_id,s.address,s.hash_value
from v$db_object_cache d,v$sqlarea s
where d.name=s.SQL_TEXT
and s.sql_id='&sql_id'

Output.

SQL> exec dbms_shared_pool.purge('C0000006B0B15310,1472305781','C',65);

PL/SQL procedure successfully completed.

SQL> /
Enter value for sql_id: arxk2v5bw36mp
old   5: and s.sql_id='&sql_id'
new   5: and s.sql_id='arxk2v5bw36mp'

no rows selected

Note: In above example 65 means the whole object from shared pool will be purged.
      i.e plan of query will be purged from library cache. And C means object "sql statement" that is not    a package/procedure/function/trigger/sequence.