« 转载新闻一则--一只小野鸭的超能量 | Blog首页 | 如何重新配置Oracle的EM Database Control »
Oracle 11g新特性:跟踪文件名称的确定
链接：https://www.eygle.com/archives/2007/09/11g_tracefile_name.html
在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(*)
----------
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用于记录跟踪文件的名称。
其他参考链接:
http://www.eygle.com/archives/2004/12/howto_get_trace_filename.html
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.
-The End-
历史上的今天...
>> 2012-09-03文章:
>> 2010-09-03文章:
>> 2009-09-03文章:
>> 2008-09-03文章:
>> 2005-09-03文章:
By eygle on 2007-09-03 14:06 | Comments (5) | Oracle12c/11g | 1579 |
其实也可以使用控制文件tracefile_identifier 参数来确定跟踪文件的名称，例如：
参数tracefile_identifie可以用来控制生成在user_dump_dest 目录下的trace文件名称
SQL> alter session set sql_trace=true ;
Session altered.
SQL> alter session set tracefile_identifier ='DEBUG';
Session altered.
在user_dump_dest 目录下的trace文件名称定义为：
INSTANCE_PID_ora_TRACEID.trc , 其中INSTANCE为ORACLE的实例名，PID为操作系统的进程ID（V$PROCESS.OSPID）,TRACEID就是设置的tracefile_identifie值。
en,这也是一个好方法：）
Here is the original one, 我忘记了原文链接, 希望原作者见谅. :)
10g and upward.
There is an undocumented alter session command that allows you to control the name of the trace file written to user_dump_dest when tracing a session. It can also be used to create multiple tracefiles from the same session.
The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc
Where INSTANCE is the name of the Oracle instance, PID is the operating system process ID (V$PROCESS.OSPID) and TRACEID is a character string of your choosing.
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=GARRY;
Use V$PROCESS.TRACEID to check the setting.
SQL> select username, traceid from v$process
USERNAME TRACEID
--------------- ----------
oracle GARRY
The file created in user_dump_dest would be named
orcl_ora_12586_GARRY.trc
还是我比较懒，做试验的时候，我一般将trace按时间排序，找最新的，哈哈^_^
跟踪其他的会话,情况是怎样呢?