DBMS SHARED POOL.PURGE

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 개요

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);

파라미터의 의미는 다음과 같다(Metalink에서 발췌)

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.  


사용법은 다음과 같다.

-- 특정 SQL Cursor를 Purge하고자 할 경우
select address, hash_value from v$sqlarea where sql_text like '....';
ADDRESS                 HASH_VALUE
------------              ---------------
2F4651EC                 3900782439

exec sys.dbms_shared_pool.purge('2F4651EC.3900782439', 'C');

-- Heap 0만 Purge할 경우
exec sys.dbms_shared_pool.purge('2F4651EC.3900782439', 'C', 1);

-- Heap 6만 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 프로시저


[편집] 외부 참조

  1. Metalink doc ID: 457309.1