Script to list events in Oracle Database

 to know what are all the events turned on in our database    

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;

    /