如果是在系统级别设置了events ， 可以通过 show parameter events 来查看。 更方便的是通过 oradebug eventdump [system | session] 来查看。非常方便，而且全面。比如10046 和 10053 两个trace ,通过 函数dbms_system.read_ev 是无法取出的，但是oradebug eventdump可以。
这是使用dbms_system.read_ev 读取会话中开启的events：
SET SERVEROUTPUT ON SIZE UNL
DECLARE
EVENT_LEVEL NUMBER;
BEGIN
FOR I IN 10000 .. 99999 LOOP
SYS.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;
/
下面是示例:
-- 查看会话初始事件状态
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump system
12641 errorstack (3) systemstate (10)
SQL> oradebug eventdump session
12641 errorstack (3) systemstate (10)
SQL> SET SERVEROUTPUT ON SIZE UNL
DECLARE
l_level NUMBER;
SQL> 2 3 BEGIN
4 FOR l_event IN 0..10999
5 LOOP
6 dbms_system.read_ev (l_event,l_level);
7 IF l_level > 0 THEN
8 dbms_output.put_line ('Event '||TO_CHAR (l_event)||' is set at level '||TO_CHAR (l_level));
9 END IF;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
-- 在当前会话设置 一些事件
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10710 trace name context forever, level 1';
Session altered.
-- 查看当前会话追踪事件状态
SQL> SET SERVEROUTPUT ON SIZE UNL
SQL> DECLARE
2 l_level NUMBER;
3 BEGIN
4 FOR l_event IN 0..10999
5 LOOP
6 dbms_system.read_ev (l_event,l_level);
7 IF l_level > 0 THEN
8 dbms_output.put_line ('Event '||TO_CHAR (l_event)||' is set at level '||TO_CHAR (l_level));
9 END IF;
10 END LOOP;
11 END;
12 /
Event 10710 is set at level 1
PL/SQL procedure successfully completed.
SQL> oradebug eventdump session
10710 trace name context forever, level 1
trace [RDBMS.SQL_OPTIMIZER]
trace [RDBMS.SQL_Transform]
trace [RDBMS.SQL_MVRW]
trace [RDBMS.SQL_VMerge]
trace [RDBMS.SQL_Virtual]
trace [RDBMS.SQL_APA]
trace [RDBMS.SQL_Costing]
trace [RDBMS.SQL_Parallel_Optimization]
trace [RDBMS.SQL_Plan_Management]
sql_trace level=12
12641 errorstack (3) systemstate (10)
SQL>
从上面的示例可以验证，oradebug eventdum 要比 dbms_system.read_ev 要全面。