Friends, many a times we will be setting events traces in our databases for various reasons. The below script will give advantage to know what are all the events turned on in our database

Listing All Events

Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use

    SET SERVEROUTPUT ON
   
    DECLARE
      err_msg VARCHAR2(120);
    BEGIN
      dbms_output.enable (1000000);
      FOR err_num IN 10000..10999
      LOOP
        err_msg := SQLERRM (-err_num);
        IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN
          dbms_output.put_line (err_msg);
        END IF;
      END LOOP;
    END;
    /

On Unix systems event messages are in the formatted text file

    $ORACLE_HOME/rdbms/mesg/oraus.msg

To print detailed event messages (Unix only) use the following script

    event=10000
    while [ $event -ne 10999 ]
    do
        event=`expr $event + 1`
        oerr ora $event
    done

Listing Enabled Events

To check which events are enabled in the current session

    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;
    /