Oracle DBA Daily Experiences

Using “oradebug dump events 1″ one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

In another session login as an account as sysdba
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:
– ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK LEVEL 3′;

It will write to the alert.log
Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off one would run the following SQL:
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
SQL> alter system set event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on event for.