Finding the Events

To find out which events you have set in your 10g oracle database use the following from SQL*Plus when connected as sysdba.

oradebug setmypid
oradebug dump events 4

Find the trace file in the user dump destination and it will contain something like this

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/product/10.2.0
System name:    Linux
Node name:      bob
Release:        2.6.9-78.0.8.ELxenU
Version:        #1 SMP Wed Nov 5 07:40:25 EST 2008
Machine:        x86_64
Instance name: fred
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 26048, image: oracle@bob (TNS V1-V3)

*** 2010-02-10 17:26:22.100
*** ACTION NAME:() 2010-02-10 17:26:22.099
*** MODULE NAME:(sqlplus@bob (TNS V1-V3)) 2010-02-10 17:26:22.099
*** SERVICE NAME:(SYS$USERS) 2010-02-10 17:26:22.099
*** SESSION ID:(197.7107) 2010-02-10 17:26:22.099
Dump event group for level SYSTEM
TC Addr  Evt#(b10)   Action   TR Addr    Arm    Life
81246A20 10298       1       81246ab0 0 0
       TR Name             TR level   TR address       TR arm     TR life    TR type
       CONTEXT                      32 0         -1          2          0 

Here we have set event 10298 at level 32 like so

alter system set events '10298 trace name context forever, level 32';

This event incidentally turns off the checking for the correct parameters used when using nfs mountpoints for the datafiles and rman backups.

To do the same in 11g use the following

alter session set events 'immediate eventdump(system)';
alter session set events 'immediate eventdump(session)';
alter session set events 'immediate eventdump(process)';

Another quick SQL way to get events set is to run the following code

SET SERVEROUTPUT ON

DECLARE
   l_level NUMBER;
BEGIN
   FOR l_event IN 10000..10999
   LOOP
       dbms_system.read_ev (l_event,l_level);
       IF l_level > 0
       THEN
           dbms_output.put_line ('Event '||TO_CHAR (l_event)||' is set at level '||TO_CHAR (l_level));
       END IF;
   END LOOP;
END;
/ 

This will catch most of the events commonly set.