How to find which even is set?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to find which even is set?

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    Hi,

    I recently set the deadlock event by altering the system (not in the init.ora). How do I find whether the event is still in effect?

    This is the command I used to set the event.
    "alter system set events '60 trace name errorstack level 3;"

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SET ECHO off
    REM NAME: TFSEVENT.SQL
    REM USAGE:"@path/tfsevent"
    REM ------------------------------------------------------------------------
    REM REQUIREMENTS:
    REM Must be run as SYS
    REM ------------------------------------------------------------------------
    REM AUTHOR:
    REM Anonymous
    REM Copyright 1996, Oracle Corporation
    REM ------------------------------------------------------------------------
    REM PURPOSE:
    REM This will report all events set in the current session and
    REM their level.
    REM ------------------------------------------------------------------------
    REM EXAMPLE:
    REM SQL> @events
    REM
    REM Event 10232 set at level 10
    REM
    REM PL/SQL procedure successfully completed.
    REM
    REM ------------------------------------------------------------------------
    REM DISCLAIMER:
    REM This script is provided for educational purposes only. It is NOT
    REM supported by Oracle World Wide Technical Support.
    REM The script has been tested and appears to work as intended.
    REM You should always run new scripts on a test instance initially.
    REM ------------------------------------------------------------------------
    REM Main text of script follows:

    set serveroutput on

    prompt

    declare
    event_level number;
    begin
    for i in 10000..10999 loop
    dbms_system.read_ev(i,event_level);
    if (event_level > 0) then
    dbms_output.put_line('Event '||to_char(i)||' set at level '||
    to_char(event_level));
    end if;
    end loop;
    end;
    /

    ----------- cut ---------------------- cut -------------- cut --------------


    =========
    Examples:
    =========

    SQL> @events

    Event 10232 set at level 10

    PL/SQL procedure successfully completed.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Posts
    194
    Hi Jm:

    Thanks for the response. I tried the PL/SQL block changing the index from 1..10999, but got nothing.

    I added the following line to the init.ora
    event="60 trace name errorstack level 3;name systemstate level 10"

    Shutdown and restarted the DB.

    connect sys/pwd@dev ;

    set serveroutput on ;

    declare
    event_level number;
    begin
    for i in 1..10999 loop
    dbms_system.read_ev(i,event_level);
    if (event_level > 0) then
    dbms_output.put_line('Event '||to_char(i)||' set at level '||
    to_char(event_level));
    end if;
    end loop;
    dbms_output.put_line('Done') ;
    end;
    /

    result:
    -------
    Done

    PL/SQL procedure successfully completed.

    Any Ideas????

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sorry, I've never used the provided script and currently I have no database to test it. Based on the date in the copyright of the script I'd say it was created for Oracle7. Maybe something in the definition of the DBMS_SYSTEM or some other internal object involved has changed since....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2000
    Posts
    194
    Hi, I got my Answer from Jonathan Lewis of http://www.jlcomp.demon.co.uk

    (for those who are interested, following is his response)
    //
    I think you may find that the read_ev call used
    in dbms_system works only for event name CONTEXT.

    An alternative approach is to invoke
    ORADEBUG dump events N
    where N is
    1 for session
    2 for process
    4 for system

    Use svrmgrl, or sqlplus with connect internal as sysdba
    in your version of Oracle.


    Jonathan Lewis
    //


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width