How to find which even is set?

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

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.

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????

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....

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