oracle 11g 删除指定的sql_id

11g中引入DBMS_SHARED_POOL.PURGE删除指定的某个sql_id不用清空shared_pool
查看包DBMS_SHARED_POOL定义
desc sys.DBMS_SHARED_POOL;
PROCEDURE PURGE
Argument Name Type In/Out Default?

---

NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
HEAPS NUMBER IN DEFAULT

关于具体参数可以查看dbmspool.sql
-- name
-- The name of the object to keep. There are two kinds of objects:
-- PL/SQL objects, triggers, sequences, types and Java objects,
-- which are specified by name, and
-- SQL cursor objects which are specified by a two-part number
-- (indicating a location in the shared pool). For example:
-- dbms_shared_pool.keep('scott.hispackage')
-- will keep package HISPACKAGE, owned by SCOTT. The names for
-- PL/SQL objects follows SQL rules for naming objects (i.e.,
-- delimited identifiers, multi-byte names, etc. are allowed).
-- A cursor can be keeped by
-- dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
-- 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 keep 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

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%';

HASH_VALUE ADDRESS PLAN_HASH_VALUE SQL_ID

---

3184406849 00000000AE2DB970 232555890 24jdvdfywwca1
2959378782 0000000112682288 2833663960 cxwwf0fs692ay
4039302930 00000001124B2A18 232555890 5622a87sc5rsk

exec DBMS_SHARED_POOL.purge('0000000112682288,2959378782','c');
alert日志会显示下面日志
Wed Jul 03 22:36:07 2019
Executed dbms_shared_pool.purge(): hash=b064895e phd=0x112682288 flags=268511297 childCnt=1 mask=1, purgeCnt=1 invalidCnt=0 ospid=12476