Script to list events in Oracle Database

Script to list events in Oracle Database

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