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