oracle gettrcname,@gettrcname.sql

http://www.eygle.com/archives/2007/05/script_gettrcname.html

最近有很多朋友问起《深入浅出Oracle》一书中的一个脚本gettrcname.sql。

这个脚本用于获取跟踪文件的名称的，可以用于Linux/Unix环境，已经被包含在书的脚本包中。

其内容如下：

SELECT    d.VALUE

|| ‘/‘

|| LOWER (RTRIM (i.INSTANCE, CHR (0)))

|| ‘_ora_‘

|| p.spid

|| ‘.trc‘ trace_file_name

FROM (SELECT p.spid

FROM v$mystat m, v$session s, v$process p

WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

(SELECT t.INSTANCE

FROM v$thread t, v$parameter v

WHERE v.NAME = ‘thread‘

AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

(SELECT VALUE

FROM v$parameter

WHERE NAME = ‘user_dump_dest‘) d

/

在Linux下执行该脚本输入类似：

SQL> @gettrcname

TRACE_FILE_NAME

---------------------------------------------------------------------------------------------------

/opt/oracle/admin/eygle/udump/eygle_ora_8415.trc

如我们执行一个跟踪操作：

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from dba_users;

COUNT(*)

----------

9

SQL> ! head /opt/oracle/admin/eygle/udump/eygle_ora_8415.trc

/opt/oracle/admin/eygle/udump/eygle_ora_8415.trc

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

ORACLE_HOME = /opt/oracle/product/9.2.0

System name: Linux

Node name: jumper.hurray.com.cn

Release: 2.4.21-15.EL

Version: #1 Thu Apr 22 00:27:41 EDT 2004

Machine: i686

收录一下blue_prince提供的更简化的脚本:

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

/

简单解释一下，供我的读者们参考。

-The End-

原文：http://www.cnblogs.com/51linux/p/4113467.html