It’s my quick testing on Julian Dyke’s posting at http://julian.dyke.users.btopenworld.com/com/Diagnostics/Events/Events.html
That’s a real good source for understanding Oracle Events.
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;
/
The output looks like
………..
ORA-10029: session logon (KSU)
ORA-10030: session logoff (KSU)
ORA-10031: sort debug event (S*)
ORA-10032: sort statistics (SOR*)
ORA-10033: sort run information (SRD*/SRS*)
ORA-10035: parse SQL statement (OPIPRS)
ORA-10036: create remote row source (QKANET)
ORA-10037: allocate remote row source (QKARWS)
ORA-10038: dump row source tree (QBADRV)
ORA-10039: type checking (OPITCA)
ORA-10040: dirty cache list
ORA-10041: dump undo records skipped
ORA-10042: trap error during undo application
ORA-10043: check consistency of owner/waiter/converter lists in KSQ
ORA-10044: free list undo operations
ORA-10045: free list update operations - ktsrsp, ktsunl
ORA-10046: enable SQL statement timing
ORA-10047: trace switching of sessions
ORA-10048: Undo segment shrink
ORA-10049: protect library cache memory heaps
ORA-10050: sniper trace
ORA-10051: trace OPI calls
ORA-10052: don’t clean up obj$
ORA-10053: CBO Enable optimizer trace
ORA-10054: trace UNDO handling in MLS
ORA-10055: trace UNDO handing
ORA-10056: dump analyze stats (kdg)
………..
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;
/
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;
/
My testing on it (create procedure sys.check_enabled_events based on code above).
SQL> alter session set events ‘10053 trace name context forever’;
Session altered.
SQL> set serveroutput on
SQL> exec sys.check_enabled_events
Event 10053 is set at level 1
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET EVENTS ‘10053 trace name context off’;
Session altered.
SQL> exec sys.check_enabled_events
PL/SQL procedure successfully completed.
Popularity: 6% [?]
