示例相关：SQL

tname.sql

select value from v$diag_info

where name = 'Default Trace File'; 

sinfo.sql

select sid,serial# from v$session where sid=&sid;

spinfo.sql

select s.sid,s.serial# from v$process p,v$session s

where p.addr=s.paddr and p.spid=&pid;

sid.sql

select sid from v$mystat where rownum<2 ;

已知session ID 对其进行跟踪

dbms_monitor

用法：

execute dbms_monitor.session_trace_enable(session_id=>&sid, serial_num=>&serial,

waits=>true,binds=>false);

关闭：

execute dbms_monitor.session_trace_disable(session_id=>&sid,serial_num=>&serial);

DBMS_MONITOR.SESSION_TRACE_ENABLE(

session_id IN BINARY_INTEGER DEFAULT NULL,

serial_num IN BINARY_INTEGER DEFAULT NULL,

waits IN BOOLEAN DEFAULT TRUE,

binds IN BOOLEAN DEFAULT FALSE,

plan_stat IN VARCHAR2 DEFAULT NULL);

示例：

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 dexter@DEX11g> @sid           SID ----------         36    sys@DEX11g> @sinfo Enter value for sid: 36 old   1: select sid,serial#from v$session where sid=&sid new   1: select sid,serial#from v$session where sid=36           SID    SERIAL# ---------- ----------         36        415      sys@DEX11g> executedbms_monitor.session_trace_enable(session_id=>&sid,serial_num=>&serial,waits= >true,binds=>false); Enter value for sid: 36 Enter value for serial: 415    PL/SQL procedure successfully completed.    dexter@DEX11g> select count(*) from t ;      COUNT(*) ----------      72523    关闭对session的跟踪    sys@DEX11g> executedbms_monitor.session_trace_disable(session_id=>&sid,serial_num= >&serial); Enter value for sid: 36 Enter value for serial: 415    PL/SQL procedure successfully completed.

dbms_system

用法：

exec dbms_system.set_ev(&sid,&serial,&event,&level,'&name');

若要关闭，只需要将level设置为0即可

dbms_syste.set_ev(&sid,&serial,&event,0, '&name') ;

这个方法比较通用

PROCEDURE SET_EV

Argument Name Type In/Out Default?

----------------------------------------------------- ------ --------

session_id BINARY_INTEGER IN

seriv# BINARY_INTEGER IN

event BINARY_INTEGER IN

level BINARY_INTEGER IN

name VARCHAR2 IN

示例

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 dexter@DEX11g> @sid           SID ----------         36    sys@DEX11g> @sinfo 36 Enter value for sid: 36 old   1: select sid,serial#from v$session where sid=&sid new   1: select sid,serial#from v$session where sid=36           SID    SERIAL# ---------- ----------         36        421    sys@DEX11g> execdbms_system.set_ev(&sid,&serial,&event,&level,'&name') ; Enter value for sid: 36 Enter value for serial: 421 Enter value for event: 10046 Enter value for level: 12 Enter value for name:    PL/SQL procedure successfully completed.

找到相应的trace文件。注意

select value from v$diag_info

where name = 'Default Trace File';

得到的trace文件的path只是针对本session的。

比如这个例子中，使用sys用户对sid为36的session进行跟踪，那么trace文件的位置可以在sid为36的

session也就是dexter用户执行

select value from v$diag_info

where name = 'Default Trace File';

才可以得到相应的trace文件。

dexter@DEX11g> select count(*) from t ;

COUNT(*)

----------

72523

关闭（设置level=0即可）：

sys@DEX11g> execdbms_system.set_ev(&sid,&serial,&event,&level,'&name') ;

Enter value for sid: 36

Enter value for serial: 421

Enter value for event: 10046

Enter value for level: 0

Enter value for name:

PL/SQL procedure successfully completed.

已知os pid 对其进行跟踪

oradebug

用法

oradebug event 10046 trace context forever , level 12 ;

oradebug event 10046 trace context off ;

示例

oradebug setospid &pid

oradebug event 10046 trace context forever , level 12 ;

oradebug event 10046 trace context off ;

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 [oracle@dex ~]$ ps-aef | grep oracledex oracle   5687  5681  0 Apr05 ?     &nbs