ORA-1652 trace Analysis

Many times we dont have the sql traced whenever we hit the ORA-1652 and we send a mail to apps team no sql found and again application team come back again to us . To avoid we can do the following :




1)Set the trace to track ORA-1652 error in the memory :



ALTER SYSTEM SET EVENTS '1652 trace name ERRORSTACK level 3';

It doesnt show in the show parameter but somewhere stored in the database memory

2) To ensure the event is present even after the bounce do the following :



alter system set event=’1652 TRACE NAME ERRORSTACK LEVEL 3' scope=SPFILE;



We do this in two step as there is no magic pill to set trace in a single statement .

In any case if we need to revert back the changes ,please do the below :



1)To unset the trace event in the memory :



ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK OFF’;



2)To unset the trace event from spfile also :



ALTER SYSTEM SET EVENT=' ' SCOPE=spfile;


Thanks to Amin Jaffer for the commands.