Alter System Set Events to Generate Trace Files on Ora Error

There are times when we as a developer want to trace an Oracle Exception and generate Trace files for that, which is being raised in another session or being raised when an Application is calling database package or procedure. Oracle have "alter system set events" clause to provide better tracing mechanism. Lets simply try to capture "ORA-00955: name is already used by an existing object" and generate trace files for this.

To start trace for this exception, we need to execute following using SYS user, after which when ever this exception will be raised at system (in any schema), the alert log will have the entry and trace files will be generated at USER_DUMP_DEST under DIAGNOSTIC_DEST.

Starting Tracing of Ora-Error

C:\Users\nimish.garg>sqlplus sys/sys@orcl as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 12:52:02 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> Show parameter DIAGNOSTIC_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      E:\ORACLE\APP\NIMISH.GARG

SQL> show parameter USER_DUMP_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      E:\oracle\app\nimish.garg\diag
                                                 \rdbms\orcl\orcl\trace
SQL> alter system set events '955 trace name errorstack level 3';
System altered.

This would dump errorstack only the first occurrence of ORA-00955 exception in a session, subsequent exceptions in that session will not cause a stack dump. Now when ever ORA-00955 will occur first time for a session, a entry will go in alert log and trace will will be generated at E:\oracle\app\nimish.garg\diag\rdbms\orcl\orcl\trace (USER_DUMP_DEST).

Lets generate exception in another session.

C:\Users\nimish.garg>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 13:02:31 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create table emp(a number);
create table emp(a number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Following entry was made in alert_orcl.log for this exception

Tue Feb 18 13:02:36 2014
Errors in file E:\ORACLE\APP\NIMISH.GARG\diag\rdbms\orcl\orcl\trace\orcl_ora_10188.trc:
ORA-00955: name is already used by an existing object
Tue Feb 18 13:02:49 2014
Dumping diagnostic data in directory=[cdmp_20140218130249], requested by (instance=1, osid=10188), summary=[abnormal process termination].

and also folder "cdmp_20140218130249" was created at "E:\oracle\app\nimish.garg\diag\rdbms\orcl\orcl\trace" (USER_DUMP_DEST), having all diagnostic data files.

To stop trace for this execption, we need to execute following using SYS user.

C:\Users\nimish.garg>sqlplus sys/sys@orcl as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 13:28:15 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> alter system set events '955 trace name context off';
System altered.

There are few other useful options with "alter system set events '### trace name errorstack level # [, options]"
1) If we want to get an error stack trace when error happens first time only in a session, Subsequent errors in that session will not cause a stack dump, then we can use :
- alter system set events '955 trace name errorstack level 3';

2) We can use following to make a session always dump errors stack on that error:
- alter system set events '955 trace name errorstack level 3, forever';

3) if we want to dump the errorstack on let say first 10 ocurrences in that session then we can use lifetime option instead of forever as:
- alter system set events '955 trace name errorstack level 3, lifetime 10';


Related Posts:
- Oracle : TKPROF simple steps