Hello all,
Having issues to purge SQL Cursor in a 10g database?
DBMS_SHARED_POOL.PURGE is available from 10.2.0.4 on, but according to “DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)”, it is event-protected.
This means you need to set event 5614566 to be able to use the purge.
Example:
sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw'; COUNT(*) ---------- 1 sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C') PL/SQL procedure successfully completed. sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw'; COUNT(*) ---------- 1 sys@PRODB10> alter session set events '5614566 trace name context forever'; Session altered. sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C') PL/SQL procedure successfully completed. sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw'; COUNT(*) ---------- 0
Pingback: SQL Query Tuning | Cloud On Hand