清除掉shared pool中某条sql语句方法

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:清除掉shared pool中某条sql语句方法

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在论坛中看到一个帖子,如何清除掉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.
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

清除掉shared pool中某条sql语句方法》有 2 条评论

  1. 惜 分飞 说:

    10.2.0.4中bug(DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1])

    [oracle@localhost ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 15 09:59:03 2012
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select count(*) from v$sqlarea;
    
      COUNT(*)
    ----------
          2886
    
    SQL> col sql_text for a30
    SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
      2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
    
    ADDRESS          HASH_VALUE SQL_TEXT
    ---------------- ---------- ------------------------------
    000000007C9BAC90  418614462 select count(*) from v$sqlarea
    
    SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
    BEGIN dbms_shared_pool.purge('000000007C9BAC90,418614462','C'); END;
    
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    
    SQL> @?/rdbms/admin/dbmspool.sql
    
    Package created.
    
    
    Grant succeeded.
    
    
    View created.
    
    
    Package body created.
    
    SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
      2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
    
    ADDRESS          HASH_VALUE SQL_TEXT
    ---------------- ---------- ------------------------------
    000000007C9BAC90  418614462 select count(*) from v$sqlarea
    
    SQL> alter session set events '5614566 trace name context forever';
    
    Session altered.
    
    SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
      2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
    
    ADDRESS          HASH_VALUE SQL_TEXT
    ---------------- ---------- ------------------------------
    000000007C9BAC90  418614462 select count(*) from v$sqlarea
    
    SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
      2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
    
    no rows selected
    
  2. 惜 分飞 说:

    删除表,也不能清除shared pool中的sql语句

    
    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> drop table xff;
    
    表已删除。
    
    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