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

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!