Oracle 11g新特性:跟踪文件名称的确定

作者：盖国强 来源:eygle.com

在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（）的引入，现在在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(*)
----------
10

SQL> 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: [e-mail] (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:([e-mail] (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
[e-mail] (PMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_pmon_4033.trc
[e-mail] (VKTM) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_vktm_4035.trc
[e-mail] (DIAG) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_diag_4039.trc
[e-mail] (DBRM) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dbrm_4041.trc
[e-mail] (PSP0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_psp0_4043.trc
[e-mail] (MMAN) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mman_4049.trc
[e-mail] (DIA0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dia0_4047.trc
[e-mail] (DBW0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dbw0_4051.trc
[e-mail] (LGWR) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_lgwr_4053.trc
[e-mail] (CKPT) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ckpt_4055.trc

PROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
[e-mail] (SMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_smon_4057.trc
[e-mail] (RECO) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_reco_4059.trc
[e-mail] (MMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mmon_4061.trc
[e-mail] (MMNL) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mmnl_4063.trc
[e-mail] (W000) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_w000_8653.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_27976.trc
[e-mail] (TNS V1-V3) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
[e-mail] (CJQ0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_cjq0_13885.trc
[e-mail] (SMCO) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_smco_4077.trc
[e-mail] (FBDA) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_fbda_4079.trc
[e-mail] (QMNC) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_qmnc_4081.trc

PROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_27935.trc
[e-mail] (q000) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_q000_4097.trc
[e-mail] (q001) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_q001_4101.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28014.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28363.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28418.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28382.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28380.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28422.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28463.trc
[e-mail] /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28467.trc

33 rows selected.