在Oracle Database 11g之前，要想获得跟踪文件的名称，通常我们需要执行一系列的查询，常用的脚本如下:
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file
FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
/
但是仍然有很多朋友会不断询问我关于这个脚本的情况，可见这个方法是有点曲折了。
在Oracle Database 11g中，Oracle以更简便的方式提供了跟踪文件的名称，这依赖于ADR（Automatic Diagnostic Repository）的引入，现在在v$diag_info视图中有这样一行数据：
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
这里的 Default Trace File就是缺省的会话跟踪文件名称：
SQL> alter session set sql_trace=true;Session altered.
SQL> select count(*) from dba_users;
COUNT(*)
----------
10SQL> alter session set sql_trace=false;
Session altered.
SQL> ! head -20 /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
Trace file /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/11.1.0
System name: Linux
Node name: test126.hurray.com.cn
Release: 2.6.18-8.el5xen
Version: #1 SMP Fri Jan 26 14:42:21 EST 2007
Machine: i686
Instance name: eygle
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 13373, image: oracle@test126.hurray.com.cn (TNS V1-V3)
*** 2007-08-28 13:50:42.772
*** SESSION ID:(140.782) 2007-08-28 13:50:42.772
*** CLIENT ID:() 2007-08-28 13:50:42.772
*** SERVICE NAME:(SYS$USERS) 2007-08-28 13:50:42.772
*** MODULE NAME:(sqlplus@test126.hurray.com.cn (TNS V1-V3)) 2007-08-28 13:50:42.772
现在获得跟踪文件的名称就简单得多了。
对于后台进程的跟踪文件名称，可以通过查询v$process视图获得，这个视图新增加了一个字段TRACEFILE用于记录跟踪文件的名称。
SQL> select program,TRACEFILE from v$process;PROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
PSEUDO /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_0.trc
oracle@test126.hurray.com.cn (PMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_pmon_4033.trc
oracle@test126.hurray.com.cn (VKTM) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_vktm_4035.trc
oracle@test126.hurray.com.cn (DIAG) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_diag_4039.trc
oracle@test126.hurray.com.cn (DBRM) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dbrm_4041.trc
oracle@test126.hurray.com.cn (PSP0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_psp0_4043.trc
oracle@test126.hurray.com.cn (MMAN) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mman_4049.trc
oracle@test126.hurray.com.cn (DIA0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dia0_4047.trc
oracle@test126.hurray.com.cn (DBW0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dbw0_4051.trc
oracle@test126.hurray.com.cn (LGWR) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_lgwr_4053.trc
oracle@test126.hurray.com.cn (CKPT) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ckpt_4055.trcPROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
oracle@test126.hurray.com.cn (SMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_smon_4057.trc
oracle@test126.hurray.com.cn (RECO) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_reco_4059.trc
oracle@test126.hurray.com.cn (MMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mmon_4061.trc
oracle@test126.hurray.com.cn (MMNL) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mmnl_4063.trc
oracle@test126.hurray.com.cn (W000) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_w000_8653.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_27976.trc
oracle@test126.hurray.com.cn (TNS V1-V3) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
oracle@test126.hurray.com.cn (CJQ0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_cjq0_13885.trc
oracle@test126.hurray.com.cn (SMCO) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_smco_4077.trc
oracle@test126.hurray.com.cn (FBDA) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_fbda_4079.trc
oracle@test126.hurray.com.cn (QMNC) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_qmnc_4081.trcPROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_27935.trc
oracle@test126.hurray.com.cn (q000) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_q000_4097.trc
oracle@test126.hurray.com.cn (q001) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_q001_4101.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28014.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28363.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28418.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28382.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28380.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28422.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28463.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28467.trc33 rows selected.
===============================================================
Level 0 tracing被关闭。这相当于设置sql_trace=false。
Level 1 标准SQL trace信息（SQL_TRACE=TRUE）。这是默认级别。
Level 4 SQL trace信息加绑定变量值。
Level 8 SQL trace信息加等待事件信息。
Level 12 SQL trace 信息，等待事件信息，和绑定变量值。
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
会话位置:USER_DUMP_DEST
后台位置:BACKGROUND_DUMP_DEST
enable
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';
-- To include Wait Event data with SQL trace (default option)
exec sys.dbms_support.start_trace;
-- To include Bind variable values, Wait Event data with SQL trace
exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)
-- Run your SQL script or program to trace wait event information
-- To turn off the tracing:
exec sys.dbms_support.stop_trace;
如何跟踪其他人的会话
如果你不能确信参数TIMED_STATISTICS和MAX_DUMP_FILE_SIZE是否对你想跟踪的会话设置合适，你应该从V$SESSION得到SID和它的序列号（SERIAL#）。在开启trace之前，你然后能做以下过程来合适得设置这些参数。
-- Set TIME_STATISTICS to TRUE for SID 1234, Serial# 56789
exec sys.dbms_system.set_bool_param_in_session( -
sid => 1234, -
serial# => 56789, -
parnam => 'TIMED_STATISTICS', -
bval => true);
-- Set MAX_DUMP_FILE_SIZE to 2147483647
-- for SID 1234, Serial# 56789
exec sys.dbms_system.set_int_param_in_session( -
sid => 1234, -
serial# => 56789, -
parnam => 'MAX_DUMP_FILE_SIZE', -
intval => 2147483647);
如果这些过程不能用于你的oracle版本（Oracle版本8.1.5和以下），你能使用ALTER SYSTEM SET <parameter>命令来设置这些参数。
方法1 使用DBMS_SUPPORT包过程
-- Enable 'level 12' trace in session 1234 with serial# 56789
exec dbms_support.start_trace_in_session( -
sid => 1234, -
serial# => 56789, -
waits => true, -
binds => true);
-- program for some amount of time
exec dbms_support.stop_trace_in_session( -
sid => 1234, -
serial# => 56789);
execute dbms_system.set_ev( 1234, 56789, 10046, 8, '');
-- program for some amount of time
execute dbms_system.set_ev( 1234, 56789, 10046, 0, '');
select s.username,
p.spid os_process_id,
p.pid oracle_process_id
from v$session s, v$process p
where s.paddr = p.addr
and s.username = upper('&user_name');
alter system set timed_statistics=true;
oradebug setospid 12345;
--12345 是会话的OS进程id
Oradebug unlimit;
Oradebug event 10046 trace name context forever ,level 8;
--让会话执行SQL脚本或程序一段时间
--关闭trace
Oradebug event 10046 trace name context off;
在ORACLE10g1版本中，你能使用DBMS_MONITOR包过程来开启基于SID，服务名，模块，或动作来跟踪。基于动作的跟踪使dba能跟踪一个指定的商业功能。这是一个捕捉：过程要求DBA知道模块和动作名。
execute dbms_monitor.session_trace_enable(1234,56789,true,true);
-- Let the session execute SQL script or
-- program for some amount of time
execute dbms_monitor.session_trace_disable(1234, 56789);
--开启级别12跟踪已知服务，模块，和动作
execute dbms_monitor.serv_mod_act_trace_enable(
'APPS1','GLEDGER','DEBIT_ENTRY',TRUE,TRUE,NULL);
--执行sql脚本或程序一段时间
--关闭tracing
Execute dbms_monitor.serv_mod_act_trace_disable(
'APPS1','GLEDGER','DEBIT_ENTRY');
用户会话的跟踪文件将被写到USER_DUMP_DEST目录，而后台进程的跟踪文件将被写到BACKGROUND_DUMP_DEST目录。跟踪文件命名在许多平台上包含.trc或TRC扩展名。
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8
Statement processed.
SQL> oradebug tracefile_name
d:\oracle\admin\or92\udump\or92_ora_171.trc
从Oracle8.1.7开始，你能为你会话设置TRACEFILE_IDENTIFIER参数，使用ALTER SESSION命令，如下显示：
alter session set tracefile_identifier='MyTrace';
