1、以qiuyb用户执行一个sqlSQL> select * from t1 where id=1;

ID NAME
---------- ----------------------------------------
1 haerbin

2、以sys用户查找这条语句sql_address及hash_valueSQL> SELECT ADDRESS,
2 HASH_VALUE,
3 SQL_ID,
4 EXECUTIONS,
5 LOADS,
6 VERSION_COUNT,
7 INVALIDATIONS,
8 PARSE_CALLS
9 FROM V$SQLAREA
10 WHERE SQL_TEXT = 'select * from t1 where id=1';

ADDRESS HASH_VALUE SQL_ID EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ------------- ---------- ---------- ------------- ------------- -----------
C000000BBEFA8408 525190722 5ag8kthgnvjk2 1 1 1 0 1

3、在Oracle 10G版本存在Bug 5614566,需设定诊断事件SQL> alter session set events '5614566 trace name context forever';

4、执行purgeSQL> exec dbms_shared_pool.purge('C000000BBEFA8408,525190722','C');

5、验证一下Cursor是不是还在SQL> SELECT ADDRESS,
2 HASH_VALUE,
3 SQL_ID,
4 EXECUTIONS,
5 LOADS,
6 VERSION_COUNT,
7 INVALIDATIONS,
8 PARSE_CALLS
9 FROM V$SQLAREA
10 WHERE SQL_TEXT = 'select * from t1 where id=1';

no rows selected

SQL>

6、过程purge说明procedure purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);

Explanation: Purge the named object or particular heap(s) of the object.
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.

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 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:

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

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.