ORACLE: Purge a cursor from the shared pool

Have you ever wanted to purge a specific SQL statement from the shared pool.
This comes in handy when you want to force a hard parse for a specific statement.


1. find the sql_id from the specific statement in the shared pool

select sql_id from v$sql where sql_text='sql text';

2. find the address + hash value of this particular statement:

SELECT address||','||hash_value, version_count  FROM v$sqlarea WHERE sql_id = 'sql id';

3. purge the statement from the shared pool using the above address+hash:

EXECUTE dbms_shared_pool.purge('address + hash','C',1);

A short example of the above steps, assumed you are looking for the sql statement:
select * from t1 where object_id=3000;

SQL> select sql_id from v$sql where sql_text='select * from t1 where object_id=3000';

SQL_ID        
------------- 
79xnbb98drf3m 

Elapsed: 00:00:00.026

SQL> SELECT address||','||hash_value FROM v$sqlarea WHERE sql_id = '79xnbb98drf3m';

ADDRESS||','||HASH_VALUE                          
------------------------------------------------- 
27197058,1356576883                               

Elapsed: 00:00:00.003

SQL> EXECUTE dbms_shared_pool.purge('27197058,1356576883','C',1);

PL/SQL procedure successfully completed.

SQL> select sql_id from v$sql where sql_text='select * from t1 where object_id=3000';

no rows selected