DBMS SHARED POOL.PURGE

Procedure、Package、Sequence、Trigger、SQL Cursorと同じLibrary Cache ObjectをShared PoolからPurgeさせる機能を提供します。このプロシージャを使用すると、Shared PoolをFlushしなくても、特定のLCOをInvalidationさせることができます。

10.2.0.4から正式にサポートされ、Back Port Patchを適用すると、10.2.0.2、10.2.0.3でも使用可能です。

Syntaxは以下の通りです。

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

Parameterの意味は次のとおりです。

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 hex 0x41 => decimal 65. so specify heaps=>65.  
         Default is 1 i.e heap 0 which means the whole object will be purged.  

使い方は以下の通りです。

 - 特定のSQL CursorをPurgeしようとする場合、
select address、hash_value from v$ sqlarea where sql_text like'....';
ADDRESS HASH_VALUE
---------------------------
2F4651EC3900782439

exec sys.dbms_shared_pool.purge（「2F4651EC.3900782439'、' C'）;

 -  Heap0のみPurgeする場合
exec sys.dbms_shared_pool.purge（「2F4651EC.3900782439'、' C'、1）;

 -  Heap6万Purgeする場合
exec sys.dbms_shared_pool.purge（「2F4651EC.3900782439'、' C'、64）;

 - 特定のProcedureをPurgeしようとする場合、
exec sys.dbms_shared_pool.purge（「OWI.TEST_PROC'、' P '）;

1.DBMS_SHARED_POOL.KEEPプロシージャ

Metalink doc ID: 457309.1