Pavan DBA's Blog

The DBA Knowledge Store

Script to list events in Oracle Database

Posted by Pavan DBA on August 23, 2012


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: