使用dbms_shared_pool.purge清除共享池中的单个SQL执行计划

dbms_shared_pool.purge可以清除共享池中的单个SQL执行计划，相比grant去刷新执行计划，甚至于 alter system flush shared_pool;影响会小的多。

SQL> select 1 from dual;

         1
----------
         1
SQL> col SQL_TEXT format a60
SQL>  set linesize 200
SQL> select SQL_TEXT,sql_id, address, hash_value
  2  from v$sql  V
  3  where v.SQL_TEXT like '%select 1 from dual%';

SQL_TEXT                                                     SQL_ID        ADDRESS          HASH_VALUE
------------------------------------------------------------ ------------- ---------------- ----------
select 1 from dual                                           520mkxqpf15q8 00000000737388D0 2866845384
select SQL_TEXT,sql_id, address, hash_value from v$sql  V wh a2jngjdpbg7pt 0000000088184360 1790418617
ere v.SQL_TEXT like '%select 1 from dual%'

清除执行计划

SQL> exec sys.dbms_shared_pool.purge('00000000737388D0,2866845384','c');

PL/SQL procedure successfully completed.

SQL> select SQL_TEXT,sql_id, address, hash_value
  2  from v$sql  V
  3  where v.SQL_TEXT like '%select 1 from dual%';

SQL_TEXT                                                     SQL_ID        ADDRESS          HASH_VALUE
------------------------------------------------------------ ------------- ---------------- ----------
select SQL_TEXT,sql_id, address, hash_value from v$sql  V wh a2jngjdpbg7pt 0000000088184360 1790418617
ere v.SQL_TEXT like '%select 1 from dual%'