联系：手机(13429648788) QQ(107644445)
链接：http://www.xifenfei.com/2521.html
作者：惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
在论坛中看到一个帖子，如何清除掉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.
|
324
到【灌水乐园】发言
10.2.0.4中bug(DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1])
[oracle@localhost ~]$ sqlplus /assysdbaSQL*Plus: Release 10.2.0.4.0 - ProductiononWed Feb 15 09:59:03 2012Copyright (c) 1982, 2007, Oracle.AllRights Reserved.Connectedto:OracleDatabase10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWiththe Partitioning, OLAP, Data MiningandRealApplication Testing optionsSQL>selectcount(*)fromv$sqlarea;COUNT(*)----------2886SQL> col sql_textfora30SQL>SELECTADDRESS,HASH_VALUE,SQL_TEXTFROMV$SQLAREA2WHERESQL_TEXTLIKE'select count(*) from v$sqlarea';ADDRESS HASH_VALUE SQL_TEXT---------------- ---------- ------------------------------000000007C9BAC90 418614462selectcount(*)fromv$sqlareaSQL>execdbms_shared_pool.purge('000000007C9BAC90,418614462','C');BEGINdbms_shared_pool.purge('000000007C9BAC90,418614462','C');END;*ERRORatline 1:ORA-06550: line 1,column7:PLS-00201: identifier'DBMS_SHARED_POOL.PURGE'must be declaredORA-06550: line 1,column7:PL/SQL: Statement ignoredSQL> @?/rdbms/admin/dbmspool.sqlPackage created.Grantsucceeded.Viewcreated.Package body created.SQL>execdbms_shared_pool.purge('000000007C9BAC90,418614462','C');PL/SQLproceduresuccessfully completed.SQL>SELECTADDRESS,HASH_VALUE,SQL_TEXTFROMV$SQLAREA2WHERESQL_TEXTLIKE'select count(*) from v$sqlarea';ADDRESS HASH_VALUE SQL_TEXT---------------- ---------- ------------------------------000000007C9BAC90 418614462selectcount(*)fromv$sqlareaSQL>altersessionsetevents'5614566 trace name context forever';Session altered.SQL>SELECTADDRESS,HASH_VALUE,SQL_TEXTFROMV$SQLAREA2WHERESQL_TEXTLIKE'select count(*) from v$sqlarea';ADDRESS HASH_VALUE SQL_TEXT---------------- ---------- ------------------------------000000007C9BAC90 418614462selectcount(*)fromv$sqlareaSQL>execdbms_shared_pool.purge('000000007C9BAC90,418614462','C');PL/SQLproceduresuccessfully completed.SQL>SELECTADDRESS,HASH_VALUE,SQL_TEXTFROMV$SQLAREA2WHERESQL_TEXTLIKE'select count(*) from v$sqlarea';norowsselected惜 分飞[引用] [回复]
删除表，也不能清除shared pool中的sql语句
SQL>selectcount(*)fromxff;COUNT(*)----------9SQL>SELECTADDRESS,HASH_VALUE,SQL_TEXTFROMV$SQLAREA2WHERESQL_TEXTLIKE'select % xff';ADDRESS HASH_VALUE SQL_TEXT-------- ---------- ------------------------------1EFB91B8 3642190903selectcount(*)fromxffSQL>droptablexff;表已删除。SQL>SELECTADDRESS,HASH_VALUE,SQL_TEXTFROMV$SQLAREA2WHERESQL_TEXTLIKE'select % xff';ADDRESS HASH_VALUE SQL_TEXT-------- ---------- ------------------------------1EFB91B8 3642190903selectcount(*)fromxff