Saturday, April 30, 2016

How to Purge SQL Statement from the Shared Pool

SQL> select sql_id from v$sqlarea
where sql_text like '%FLOOR(((NVL(SODEODLOG_END_DATE,SYSDATE)-SODEODLOG_START_DATE)*24*60*60)/3600)%'  2
  3  ;

SQL_ID
-------------
73hyvwg8qfmv3
1175vqgptmgdg

SQL> select address, hash_value from v$sqlarea where sql_id like '73hyvwg8qfmv3';

ADDRESS          HASH_VALUE
---------------- ----------
0700000D2D1E6470 3513208675

SQL> select address, hash_value from v$sqlarea where sql_id like '1175vqgptmgdg';

ADDRESS          HASH_VALUE
---------------- ----------
0700000E4ED800E0 3952721327

SQL> exec dbms_shared_pool.purge('0700000D2D1E6470, 3513208675','C');

PL/SQL procedure successfully completed.

SQL> exec dbms_shared_pool.purge('0700000E4ED800E0, 3952721327','C');

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

No comments:

Post a Comment