oracle 11g 删除指定的sql_id

1 11g中引入DBMS_SHARED_POOL.PURGE删除指定的某個sql_id不用清空shared_pool
 2 檢視包DBMS_SHARED_POOL定義
 3 desc sys.DBMS_SHARED_POOL;
 4 PROCEDURE PURGE
 5  Argument Name            Type            In/Out Default?
 6  ------------------------------ ----------------------- ------ --------
 7  NAME                VARCHAR2        IN
 8  FLAG                CHAR            IN     DEFAULT
 9  HEAPS                NUMBER            IN     DEFAULT
10 
11  關于具體參數可以檢視dbmspool.sql
12    --    name
13   --      The name of the object to keep.  There are two kinds of objects:
14   --      PL/SQL objects, triggers, sequences, types and Java objects,
15   --      which are specified by name, and 
16   --      SQL cursor objects which are specified by a two-part number
17   --      (indicating a location in the shared pool).  For example:
18   --        dbms_shared_pool.keep('scott.hispackage')
19   --      will keep package HISPACKAGE, owned by SCOTT.  The names for
20   --      PL/SQL objects follows SQL rules for naming objects (i.e., 
21   --      delimited identifiers, multi-byte names, etc. are allowed).
22   --      A cursor can be keeped by
23   --        dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
24   --    flag
25   --      This is an optional parameter.  If the parameter is not specified,
26   --        the package assumes that the first parameter is the name of a
27   --        package/procedure/function and will resolve the name.  Otherwise,
28   --        the parameter is a character string indicating what kind of object
29   --        to keep the name identifies.  The string is case insensitive.
30   --        The possible values and the kinds of objects they indicate are 
31   --        given in the following table:
32   --        Value        Kind of Object to keep
33   --        -----        ----------------------
34   --          P          package/procedure/function
35   --          Q          sequence
36   --          R          trigger
37   --          T          type
38   --          JS         java source
39   --          JC         java class
40   --          JR         java resource
41   --          JD         java shared data
42   --          C          cursor
43 
44  SQL> select a.HASH_VALUE,a.ADDRESS,a.PLAN_HASH_VALUE,a.SQL_ID from v$sqlarea a where a.SQL_TEXT like '%scott.emp%';
45 
46 HASH_VALUE ADDRESS          PLAN_HASH_VALUE SQL_ID
47 ---------- ---------------- --------------- -------------
48 3184406849 00000000AE2DB970       232555890 24jdvdfywwca1
49 2959378782 0000000112682288      2833663960 cxwwf0fs692ay
50 4039302930 00000001124B2A18       232555890 5622a87sc5rsk
51 
52 exec DBMS_SHARED_POOL.purge('0000000112682288,2959378782','c');
53 alert日志會顯示下面日志
54 Wed Jul 03 22:36:07 2019
55  Executed dbms_shared_pool.purge(): hash=b064895e phd=0x112682288 flags=268511297 childCnt=1 mask=1, purgeCnt=1 invalidCnt=0 ospid=12476      

轉載于:https://www.cnblogs.com/omsql/p/11129561.html