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 的最短，我認為最好。