获取trace 文件名称及路径的脚本:
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;
TRACE_FILE
------------------------------------------------------------
/DBBK/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20101.trc
这个脚本由四张表组成
表1结果:
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest';
VALUE
--------------------------------------------------------------
/DBBK/oracle/diag/rdbms/orcl/orcl/trace
表2结果(其实这里完全可以由'/'代替):
SELECT SUBSTR(VALUE, -6, 1) SYMBOL FROM V$PARAMETER WHERE NAME = 'user_dump_dest'
SYM
---
/
表3结果:
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
-------------------
orcl
表4结果(获取当前的实例进程号):
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
SPID
----------------------
20101
我想表2的意义在于了解substr()这个函数的用法,再简化后的脚本:
SELECT a.VALUE || '/' || b.instance_name || '_ora_' || c.spid ||
'.trc' trace_file
FROM (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') a,
(SELECT INSTANCE_NAME FROM V$INSTANCE) b,
(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) c
另外博客原本提到的语句也值得了解一下:
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;
脚本来源eygle博客 http://www.eygle.com/archives/2007/05/script_gettrcname.html
转载于:https://blog.51cto.com/onlinekof2001/1598933