oracle的系统文件路径,Oracle 用户Trace 文件路径

用户SQL Trace 的文件路径。

下面载录几个和我写的一个。

--网上的一些解答:

SELECT c.VALUE || '/' || d.instance_name || '_ora_' || a.spid || '.trc' TRACE

FROM v$process a, v$session b, v$parameter c, v$instance d

WHERE a.addr = b.paddr

AND b.audsid = USERENV ('sessionid')

AND c.NAME = 'user_dump_dest'

/disk/oracle/admin/study/udump/study_ora_13015.trc

eygle.com said:

SELECT    d.VALUE

|| '/'

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

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

FROM (SELECT p.spid

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

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

(SELECT t.INSTANCE

FROM SYS.v$thread t, SYS.v$parameter v

WHERE v.NAME = 'thread'

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

(SELECT VALUE

FROM SYS.v$parameter

WHERE NAME = 'user_dump_dest') d

/disk/oracle/admin/study/udump/study_ora_13015.trc

René Nyffenegger said:

select

u_dump.value   || '/'     ||

db_name.value  || '_ora_' ||

v$process.spid ||

nvl2(v$process.traceid,  '_' || v$process.traceid, null )

|| '.trc'  "Trace File"

from

v$parameter u_dump

cross join v$parameter db_name

cross join v$process

join v$session

on v$process.addr = v$session.paddr

where

u_dump.name   = 'user_dump_dest' and

db_name.name  = 'db_name'        and

v$session.audsid=sys_context('userenv','sessionid');

/disk/oracle/admin/study/udump/study_ora_13015.trc

Tom kytes:

select c.value || '/' || instance || '_ora_' ||

ltrim(to_char(a.spid,'fm99999')) || '.trc'

from v$process a, v$session b, v$parameter c, v$thread c

where a.addr = b.paddr

and b.audsid = userenv('sessionid')

and c.name = 'user_dump_dest'

/disk/oracle/admin/study/udump/study_ora_13015.trc

Lis Li said:

SELECT p1.value||'/'||p2.value||'_ora_'||p.spid||'.trc' filename

FROM

v$process p,

v$session s,

v$parameter p1,

v$parameter p2

WHERE p1.name = 'user_dump_dest'

AND p2.name = 'db_name'

AND p.addr = s.paddr

AND s.audsid = USERENV ('SESSIONID');

/disk/oracle/admin/study/udump/study_ora_13015.trc

从SQL 长度上来看Tom 的最短，我认为最好。