联系:手机(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.
|
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
惜 分飞[引用] [回复]
删除表,也不能清除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