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

在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

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: 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.trc

PROGRAM 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.trc

PROGRAM 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.trc

33 rows selected.

查看本文来源