让游标失效的方法总结

让游标失效的方式：
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