How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package
Last updated on JUNE 05, 2018
Applies to:
PL/SQL - Version 10.2.0.2 to 11.1.0.7 [Release 10.2 to 11.1]Oracle Database - Enterprise Edition - Version 10.2.0.2 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
Goal
NOTE! This note is only applicable for 10.2 and 11.1 database. For 11.2 and higher version, there are more options available for this package, please check the online documentation "PL/SQL Packages and Types Reference" for more details.
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< Default is 1 i.e heap 0 which means the whole object will be purged.
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< Default is 1 i.e heap 0 which means the whole object will be purged.
Solution
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms