3.对其他用户session设置
' X; j, p" @% O+ A) \. Z1 a" p 通过DBMS_SYSTEM.SET_EV系统包来实现:1 Z; v5 D7 _9 q* @: |
SQL> desc dbms_system
% w: ~, e. F0 I ...# n' ~0 i3 \. A& k
PROCEDURE SET_EV
) e- }$ L* B, x) _" V' k Argument Name Type In/Out Default?
8 u8 A* u+ P; |& G. g( T2 O --------------- ----------------------- ------ --------
* _$ s8 n! K( e7 | SI BINARY_INTEGER IN
3 S, T% m/ C) O9 F SE BINARY_INTEGER IN
4 t0 }6 D" W X# K EV BINARY_INTEGER IN* l' P( b' ^: T+ c* Z1 H6 U* ~
LE BINARY_INTEGER IN
+ M U3 A. W: D: u! K. E NM VARCHAR2 IN
. s3 W) C+ o/ _4 b4 |- ` ...
9 U8 I* H( c* I0 O/ ?6 p. @ 其中的参数SI、SE来自v$session视图。: S; A: U2 I0 l7 a$ |
查询获得需要跟踪的session信息:
$ v! [% i( _) c SQL> select sid,serial#,username from v$session
z3 O# j( S: a1 T/ H: G where username is not null;SID SERIAL# USERNAME
6 P# U) P+ R) S0 |$ t' h ---------- ---------- ----------------------- P& }2 O S6 U+ g7 s4 x& c
8 2041 SYS2 A2 P6 t+ M6 d Q
9 437 EYGLE
) L" n$ z, g6 G' S3 Y4 f 执行跟踪:
3 c6 e$ d' L% |' e" } SQL> exec dbms_system.set_ev(9,437,10046,8,'eygle');
7 n" y1 z& u4 q7 v5 p PL/SQL procedure successfully completed.
6 U* P. |& I5 N- D# V4 Y4 l" a 结束跟踪:
, g& x& j" k1 V S SQL> exec dbms_system.set_ev(9,437,10046,0,'eygle');4 e& j3 B) v5 p6 Z4 B
PL/SQL procedure successfully completed.6 V; D" J: e) c1 F# o4 g
获取跟踪文件
% J/ R) n4 h+ j 上面生成的跟踪文件位于user_dump_dest目录中,位置及文件名可以通过下面的SQL查询得到:3 y. c9 R+ Q+ L2 a3 n! _; X. U
SQL> select 2 d.value||'/'||lower(rtrim(i.instance,- D) H7 c' D1 ~: ]+ S% Z) Q g
chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 3
( o& s" h9 r, K from 4 ( select p.spid 5 from sys.v$mystat m,- f4 t" Y6 f! q! `
sys.v$session s,sys.v$process p 6
+ J: J5 {+ o( N; |6 Z) R6 \ where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,1 _, r! h0 n0 N9 i8 \+ c. a# Y
7 ( select t.instance from sys.v$thread t,sys.v$parameter
* R" h! K: ?5 J! t5 D v 8 where v.name = 'thread' and% E, z2 J; |3 R
(v.value = 0 or t.thread# = to_number(v.value))) i, 99 ?1 D i3 s/ c7 Z; e; j: R
( select value from sys.v$parameter6 S2 {. P! b/ M6 `6 M
where name = 'user_dump_dest') d 10 /9 [! n- G( l F- P) |
TRACE_FILE_NAME
% `5 G8 f5 j6 V* N; X/ H" v ----------------------------8 R. l4 `: {# Y2 S1 b9 d/ Y
/opt/oracle/admin/hsjf/udump/hsjf_ora_1026.trc( B/ ]# ^, f; E5 r1 z$ V0 }* F, L
读取当前session所设置的参数6 `% G" j8 m/ O- s
假如我们通过alter session的方式设置了sql_trace,此设置在正常情况下是不能通过show parameter方式获取的,需要我们通过dbms_system.read_ev来获取:
4 e% ~2 e' y! _ SQL> set feedback offSQL> set serveroutput on SQL> declare t# r3 X: a) M' C# g% E" E$ }3 e
2 event_level number;
$ M& M# B; A4 e8 u1 t4 @ 3 begin
( p ], h) c" B% g8 d- L& K- s" v 4 for event_number in 10000..10999 loop
2 G, w) U5 L* I2 n. ~( t 5 sys.dbms_system.read_ev(event_number, event_level);) c$ z- ~3 b7 R; y2 x( J1 R
6 if (event_level > 0) then( t3 Y* @3 ]2 M# ~8 \
7 sys.dbms_output.put_line(& y/ U8 U: a2 i" R& }
8 'Event ' ||
' p. r9 P: N$ j) t) b4 R- I 9 to_char(event_number) ||6 V: O7 g) O6 h' n. n- W
10 ' is set at level ' ||/ j/ W0 U3 I9 W2 i' b
11 to_char(event_level)
, T2 [( z, z0 B- i6 r 12 );
- y. U5 Z1 o" P 13 end if;
; I4 P+ c6 q! S3 E& w1 `7 Q 14 end loop;0 f! j6 K/ |+ {9 _. J( u. ]
15 end;; M% x2 Q N7 W8 W- C) }
16 /& D$ R; u' f5 |& L* b' n3 @
Event 10046 is set at level |