select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9tgj4g8y4rwy8';

OBJECT_STATUS                          ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS IS
-------------------------------------- ---------------- ---------- ---------- ----------- --
VALID                                  00000000B37CBC98 1011610568      18995       18995 Y
VALID                                  00000000B37CBC98 1011610568        368         368 Y
VALID                                  00000000B37CBC98 1011610568      43600       43600 N
VALID                                  00000000B37CBC98 1011610568      36455       36455 N
VALID                                  00000000B37CBC98 1011610568       8319        8319 N
VALID                                  00000000B37CBC98 1011610568      30454       30454 N
VALID                                  00000000B37CBC98 1011610568         51          51 y

exec dbms_shared_pool.purge('00000000B37CBC98,1011610568','C');

select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9tgj4g8y4rwy8';
VALID                                  00000000B37CBC98 1011610568      18995       18995 N
VALID                                  00000000B37CBC98 1011610568        368         368 N
VALID                                  00000000B37CBC98 1011610568      43600       43600 N
VALID                                  00000000B37CBC98 1011610568      36455       36455 N
VALID                                  00000000B37CBC98 1011610568       8319        8319 N
VALID                                  00000000B37CBC98 1011610568      30454       30454 N
VALID                                  00000000B37CBC98 1011610568         51          51 N

Bug 11829677

11.1.0.7升到11.1.0.7.8后貌似这个貌似这个问题解决了，但是并没发现7.8这个版本里有这个bugfix，奇怪。。MOS上也没说7.8这个版本解决了这个BUG

select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9gkq7rruycsjp';

OBJECT_STATUS                          ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS IS
-------------------------------------- ---------------- ---------- ---------- ----------- --
VALID                                  00000005BFED2DA8 4125516341          1           1 Y
VALID                                  00000005BFED2DA8 4125516341         97          97 Y
VALID                                  00000005BFED2DA8 4125516341        115         115 Y

exec dbms_shared_pool.purge('00000005BFED2DA8,4125516341','C');

select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9gkq7rruycsjp';

no rows selected

opatch  lsinventory -bugs_fixed | grep 11829677，查询为空

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.1
Versions confirmed as being affected	11.1.0.7
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

12.1 (Future Release) 11.2.0.3

Description

Cursors that had V$SQL.IS_SHAREABLE set to 'N' could not be purged from the
shared pool. This has been corrected.

这个特性在10.2.0.4就有了，但是10.2.0.4要靠一个事件去激活，否则也会不起作用。

10.2.0.4:
SQL> create table test(id int);
表已创建。
SQL> select * from test;
未选定行
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000040229F039E0 1689401402 1 1

QL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');
PL/SQL 过程已成功完成。
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000040229F039E0 1689401402 1 1
 
 
SQL> alter session set events '5614566 trace name context forever';
会话已更改。
SQL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');
PL/SQL 过程已成功完成。
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';
未选定行