很多时候,我们在数据库中设置了event,如何确认设置的event生效或者如何确认你的库中设置了什么event.下面的文章测试了在11g中比较方便的方法
数据库版本
SQL>
select
*
from
v$version;
BANNER
--------------------------------------------------------------------------------
Oracle
Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS
for
Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
设置测试event
SQL>
alter
session
set
events
'10510 trace name context forever,level 1'
;
Session altered.
SQL>
alter
session
set
events
2
'10046 trace name context forever,level 4'
;
Session altered.
SQL>
alter
system
set
events
'60025 trace name context forever'
;
System altered.
SQL>
alter
system
set
events
'10513 trace name context forever,level 2'
;
System altered.
测试spfile参数中是否有event
SQL>
create
pfile=
'/tmp/pfile'
from
spfile;
File created.
solaris*orcl-/home/oracle$ grep -i event /tmp/pfile
--无记录
SQL> show parameter event;
NAME
TYPE VALUE
------------------------------------ ----------- -----------------
event string
xml_db_events string enable
查询会话event
--dbms_system实现
SQL>
set
serveroutput
on
size
1000000
SQL>
declare
2 event_level number;
3
begin
4
for
i
in
1..100000 loop
5 sys.dbms_system.read_ev(i,event_level);
6 if (event_level > 0)
then
7 dbms_output.put_line(
'Event '
||to_char(i)||
' set at level '
||
8 to_char(event_level));
9
end
if;
10
end
loop;
11
end
;
12 /
Event 10510
set
at
level
1
Event 10513
set
at
level
2
Event 60025
set
at
level
1
PL/SQL
procedure
successfully completed.
--oradebug实现
SQL> oradebug SETMYPID
Statement processed.
SQL> oradebug eventdump session
10510 trace
name
context forever,
level
1
10513 trace
name
context forever,
level
2
60025 trace
name
context forever
sql_trace
level
=4
测试证明使用dbms_system可以捕获到event,oradebug可以捕获到本身会话,还可以通过setospid/setorapid来跟踪其他会话的event设置情况.event 10046对应的本质是sql_trace所以使用dbms_system不能捕获到10046
原文地址:http://www.xifenfei.com/2012/10/%E5%A6%82%E4%BD%95%E6%9F%A5%E8%AF%A2%E4%BC%9A%E8%AF%9D-event.html