How To Purge A SQL In 10.2.0.4 And 10.2.0.5

Today I was purge a SQL in an old 10.2.0.5 database, I used the DBMS_SHARED_POOL, but it don’t work. Look for notes in ML/MOS I find in this version there is a bug, you can workaround it by set the event 5614566.

I posted a entry some time ago about how to purge simple sql from buffer cache, you can see it following the nex link;

https://sql1.wordpress.com/2014/03/20/get-out-of-here/

Below I show an example using this event;

 
SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         1

1 fila seleccionada.

SBBD*ANTO> alter session set events '5614566 trace name context forever';

Sesión modificada.

SBBD*ANTO> exec SYS.DBMS_SHARED_POOL.PURGE ('0000000507C52778,720914062', 'S');

Procedimiento PL/SQL terminado correctamente.

SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         0

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s