让游标失效的方式：
1) grant,revoke
2) 收统计信息
3）建索引，修改表结构等
4） flush shared_pool 风险太大
5）使用dbms_shared_pool.purge，建议使用，影响{BANNED}{BANNED}最佳佳小
两个参数，{BANNED}中国第一个参数是address和hash_value中间逗号分隔
exec sys.dbms_shared_pool.purge('00007FFEB35AF1C8'||','||'4034758572','C');
dingjun123@ORCLPDB> select sql_id,sql_text ,child_number from v$sql where sql_id='54uxmfzs7v1xc';
SQL_ID
--------------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
CHILD_NUMBER
------------
54uxmfzs7v1xc
select count(*) from t3 where object_name='a'
0
54uxmfzs7v1xc
select count(*) from t3 where object_name='a'
1
--清除指定SQL游标
exec sys.dbms_shared_pool.purge('00007FFEB35AF1C8'||','||'4034758572','C');
--重新执行只有一个
dingjun123@ORCLPDB> select count(*) from t3 where object_name='a';
COUNT(*)
----------
0
1 row selected.
dingjun123@ORCLPDB> select address,hash_value,sql_text ,child_number from v$sql where sql_id='54uxmfzs7v1xc';
ADDRESS HASH_VALUE
---------------- ----------
SQL_TEXT
----------------------------------------------------------------------------------------------------
CHILD_NUMBER
------------
00007FFEB35AF1C8 4034758572
select count(*) from t3 where object_name='a'
0
