Oracle 23c New Features for DBA's
How to purge a single statement from shared pool?
Additional info. (Thanks to my friend colleague for updating about the version its possible and the event)
For 10g.
The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.
10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.
Also with out the following the event never get enabled, hence you need to set the following event before executing purge procedure with DBMS_SHARED_POOL
alter session set events '5614566 trace name context forever';
For 11g,
No event set required.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc739202021a%';
ADDRESS HASH_VALUE
---------------- ----------
000000085FAS77CF0 808728292
exec DBMS_SHARED_POOL.PURGE ('000000085FAS77CF0,808728292, 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc739202021a%';
no rows selected
>>alter session set events ’5614566 trace name context forever’; in 10.2.0.4/5
That I missed. Thanks for Pointing out ,Geek DBA.