联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在论坛中看到一个帖子,如何清除掉shared pool中某条sql语句,如果是在10g以前的版本,那估计会比较麻烦,为了删除一条sql语句记录,需要清空整个shared pool,在10g中提供了新的dbms_shared_pool包可以实现该功能(如果该包没有安装,可以通过?/rdbms/admin/dbmspool.sql安装),下面我通过在11g中试验证明该问题
1.数据库版本信息
SQL> select * from v$version; BANNER ---------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
2.模拟一条sql语句
SQL> create table xff as 2 select * from dba_tables 3 where rownum<10; 表已创建。 SQL> select count(*) from xff; COUNT(*) ---------- 9 SQL> col sql_text for a30 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff
3.打破神话一:权限操作
对表进行权限操作,可以清除该表在shared pool中关于该表记录
SQL> grant select on xff to chf; 授权成功。 SQL> col sql_text for a30 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff SQL> revoke select on xff from chf; 撤销成功。 SQL> col sql_text for a30 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff
证明grant和revoke操作没有清除shared pool中关于该表的sql语句
4.打破神话二:ddl操作
对表进行ddl操作,可以清除该表在shared pool中关于该表记录
SQL> alter table xff add owner1 varchar2(100); 表已更改。 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff
事实证明ddl操作不能达到预期效果,没有清除掉这条sql语句
5.刷新shared pool
SQL> alter system flush shared_pool 2 ; 系统已更改。 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; 未选定行
把整个shared pool都刷新了,自然其中的一条sql语句也没有了,在10g前只能通过这种方法实现
6.使用dbms_shared_pool.purge
SQL> select count(*) from xff; COUNT(*) ---------- 9 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff SQL> exec dbms_shared_pool.purge('1EFB91B8, 3642190903','C'); PL/SQL 过程已成功完成。 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; 未选定行
试验证明在shared pool中清除了一条sql记录
7.关于dbms_shared_pool.purge参数说明
purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1); -- 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') -- The complete hexadecimal address must be in the first 8 characters. -- The value for this identifier is the concatenation of the -- 'address' and 'hash_value' columns from the v$sqlarea view. This -- is displayed by the 'sizes' call above. -- Currently 'TABLE' and 'VIEW' objects may not be keeped. -- -- 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 -- If and only if the first argument is a cursor address and hash-value, -- the flag parameter should be set to 'C' (or 'c'). -- -- 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.
10.2.0.4中bug(DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1])
删除表,也不能清除shared pool中的sql语句