因为在系统繁忙的时侯 使用 alter system flush shared_pool 是很危险的，
不过需要注意一点，在10.2.0.4中，虽然PURGE过程已经存在，但是要使这个过程可以真正的生效，还必须设置一个EVENT：
SQL> alter system set event = '5614566 trace name context forever' scope = spfile;
System altered.
设置EVENT后需要重启，DBMS_SHARED_POOL的PURGE才可以生效。也就是说，除非提前进行过设置，否则这个PURGE的功能对于一个产品环境而言，必须在10.2.0.5以上版本才可以使用。
Input arguments:
name: The name of the object to purge.
There are two kinds of objects:
PL/SQL objects, triggers, sequences, types and Java objects which are specified by name,
SQL cursor objects which are specified by a twopart number. The value for this identifier
is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view.
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 purge the name identifies. The string is case insensitive.
The possible values and the kinds of objects they indicate are
given in the following table:
----- ----------------------
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
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.
SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C');
This feature was introduced via the fix in bug 5614566 and I actually know a customer who has this applied on top of 10.2.0.3.
Here is an example is using dbms_shared_pool.purge to remove RAM for a specific cursor from the shared pool library cache:
SQL> exec dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge heap 0 and heap 6
PL/SQL procedure successfully completed.
This would actually not work against a cursor which is currently executing.(pinned)
Session 1:
=========
Do a massive Merge Join Cartesian
select * from dba_objects a, dba_objects b, dba_objects c;
Session 2:
=========
Identify the sql address and hash value and try to purge the cursor..
exec dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This hangs
and this session is waiting on "cursor: pin X" requesting an exclusive mutex pin for the cursor object whilst it has already been pinned by session 1
Session 3
==========
select event,p1,p2 from v$session where username='SYS' and type='USER';
EVENT P1 P2
----------------------------------------- ---------- ----------
cursor: pin X 3691928467 1
The p1 value here is the Hash value of the cursor we are trying to flush.
From the short stack of the process which is executing the purge API a function called kxsPurgeCursor is called which would try to take a mutex (since _kks_use_mutex_pin is TRUE by default) The purge completes only after you cancel the sql in session 1 and exit from the same or kill the session executing the SQL.
Set the event 5614566 in the init.ora to turn purge on.
event=”5614566 trace name context forever”
SQL> select address, hash_value from v$sqlarea where sql_text = ’select * from dept’;
ADDRESS HASH_VALUE
——– ———-
2671F27C 3599690174
SQL> exec dbms_shared_pool.purge(’2671F27C,3599690174′,’C');
PL/SQL procedure successfully completed.
SQL> select address, hash_value from v$sqlarea where sql_text = ’select * from dept’;
no rows selected
如果没有dbms_shared_pool , 可以通过 $ORACLE_HOME/rdbms/admin/dbmspool.sql 来创建。
在10.2.0.2/3 也提供了相应的patch There exists patches for some platforms in 10.2.0.2 and 10.2.0.3 downloadable as Patch 5614566.
457309.1 How To Flush an Object out the Library Cache [SGA]
751876.1 DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL>
select sql_id, address, hash_value, executions, loads, parse_calls,
invalidations
from v$sqlarea where sql_text = 'select count(*) from dual';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS
LOADS PARSE_CALLS INVALIDATIONS
------------- ----------------
---------- ---------- ---------- ----------- -------------
4m94ckmu16f9k
00000000B6C61FC0 4094900530 1 1 1
0
SQL> select 1 from dual;
1
----------
1
SQL> select * from dual;
D
-
X
SQL> select 'a' from dual;
'
-
a
SQL> select count(1) from dual;
COUNT(1)
----------
1
SQL>
select sql_id, address, hash_value, executions, loads, parse_calls,
invalidations
2 from
v$sqlarea
3 where lower(sql_text) like
'%dual%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS
LOADS PARSE_CALLS INVALIDATIONS
------------- ----------------
---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6
00000000B6A5A470 418666214 1 1
1 0
40p7rprfbt1as 00000000B69BDC38 3703342424
1 1 1 0
520mkxqpf15q8
00000000B6DD9610 2866845384 1 1 1
0
ak90gdq0udv37 00000000B6E3C6B0 2175200359 2
2 2 1
4m94ckmu16f9k 00000000B6C61FC0
4094900530 1 1 1
0
a5ks9fhw2v9s1 00000000B698DA88 942515969
1 1 1 0
800hwktjz3zuc
00000000B6999268 1676803916 1 1 1
0
7 rows selected.
SQL>
grant select on dual to sys;
grant select on dual to
sys
*
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE
privileges to/from yourself
SQL> grant select on dual to public;
Grant succeeded.
SQL>
select sql_id, address, hash_value, executions, loads, parse_calls,
invalidations
2 from
v$sqlarea
3 where lower(sql_text) like
'%dual%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS
LOADS PARSE_CALLS INVALIDATIONS
------------- ----------------
---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6
00000000B6A5A470 418666214 2 1
2 0
ak90gdq0udv37 00000000B6E3C6B0 2175200359
2 2 2 1
对于其他用户而言，都可以使用将表的查询权限授权给OWNER本身的方法，但是测试用户本身为SYS，因此需要其他用户授权，方便起见使用了授权给PUBLIC的方式。
可以看到，这种方式同样可以生效，但是仍然存在打击面过大的问题。对于系统中一个频繁访问的表，很可能这个授权的操作，导致少则几十，多则几百个SQL都是失效，
这个风险仍然不可小觑。