示例相关：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);
示例：
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
示例
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 cont