In this Document
  Goal
  Solution
  References

PL/SQL - Version: 10.2.0.2 to 11.2.0.1 - Release: 10.2 to 11.2
Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 11.2.0.1.0   [Release: 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 06-Jul-2010***

This article explains and illustrates how the PURGE procedure in the DBMS_SHARED_POOL package can be used to flush a specific object, such as a cursor, out of the Object Library Cache.

The syntax for the DBMS_SHARED_POOL.PURGE package is:

procedure purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);  

 Explanation: Purge the named object or particular heap(s) of the object.  
 Input arguments:  
  name: The name of the object to purge. 
        There are two kinds of objects:  
         PL/SQL objects, triggers, sequences, types and Java objects which are specified by name,
         SQL cursor objects which are specified by a twopart number. The value for this identifier
         is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view. 

  flag: This is an optional parameter. If the parameter is not specified,  
        the package assumes that the first parameter is the name of a  
        package/procedure/function and will resolve the name. Otherwise,  
        the parameter is a character string indicating what kind of object  
        to purge the name identifies. The string is case insensitive.  
        The possible values and the kinds of objects they indicate are  
        given in the following table:  

        Value Kind of Object to keep  
        ----- ----------------------  
            P package/procedure/function  
            Q sequence  
            R trigger  
            T type  
           JS java source  
           JC java class  
           JR java resource  
           JD java shared data  
            C cursor  

  heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.  
         1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.  
         Default is 1 i.e heap 0 which means the whole object will be purged. 

SESSION 1

sqlplus scott/tiger
SQL> select ename from emp where empno=7900;

SESSION 2 

sqlplus / as sysdba

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls
     from v$sqlarea
     where sql_text = 'select ename from emp where empno=7900';

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
000000007A6CF430 1052545619          1          1             1             0           1

SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C');

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls
     from v$sqlarea
     where sql_text = 'select ename from emp where empno=7900';

no rows selected

SESSION 1

SQL> select ename from emp where empno=7900;


SESSION 2

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls
     from v$sqlarea
     where sql_text = 'select ename from emp where empno=7900';

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS 
---------------- ---------- ---------- ---------- ------------- ------------- ----------- 
000000007A6CF430 1052545619          1          2             1             1           1

On 11.2.0.2 you get the correct result without setting the    event:

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_text = 'select ename from emp where empno=7900';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
---------------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
0000000382E80750 1052545619 1 1 1 0
1


SQL> exec dbms_shared_pool.purge ('0000000382E80750,1052545619','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_text = 'select ename from emp where empno=7900';

no rows selected

The PURGE procedure was introduced in Oracle 11g and will be included in the 10.2.0.4 patchset release. The purge procedure is event-protected in 10.2.0.4 and needs to be enabled through event 5614566, please see Note:751876.1 for more information.

There exists patches for some platforms in 10.2.0.2 and 10.2.0.3 downloadable as Patch:5614566.

BUG:5614566 - WE NEED A FLUSH CURSOR INTERFACE
PATCH:5614566 - WE NEED A FLUSH CURSOR INTERFACE

In this Document
  Goal
  Solution
  References

Platforms: 1-914CU;

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

This article is intended for DBAs who manually want to purge library cache heaps on a 10.2.0.4 database.

Session 1
===========

conn scott/tiger

SQL> select * from dept where deptno=10;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK



Session 2
===========

conn / as sysdba

SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = 'select * from dept where deptno=10';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
-------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
28F1FCB4 911274289 1 1 1 0
1

SQL> exec dbms_shared_pool.purge ('28F1FCB4,911274289','C');

PL/SQL procedure successfully completed.


SQL> select address, hash_value, executions, loads, version_count,
invalidations, parse_calls
from v$sqlarea where sql_text = 'select * from dept where deptno=10';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
-------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
28F1FCB4 911274289 1 1 1 0
1

====== Here no rows should have been selected.
.
.
Again go to session 1

.
.
SQL> select * from dept where deptno=10;
.
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

Now go to session 2
===================

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
-------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
28F1FCB4 911274289 2 1 1 0
2

Here you can see the DBMS_SAHRED_POOL.PURGE is not working as expected.

DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available
through the fix for Bug 5614566. However, the fix is event protected.  You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect.

Set the event 5614566 in the init.ora to turn purge on.

event="5614566 trace name context forever"


SQL> select address, hash_value from v$sqlarea where sql_text = 'select * from dept';

ADDRESS HASH_VALUE
-------- ----------
2671F27C 3599690174

SQL> exec dbms_shared_pool.purge('2671F27C,3599690174','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value from v$sqlarea where sql_text = 'select * from dept';

no rows selected

BUG:5614566 - WE NEED A FLUSH CURSOR INTERFACE
BUG:7538951 - DBMS_SHARED_POOL IS NOT WORKING AS EXPECTED