10g:
/* 11g依然有效 */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;
11g:select value from v$diag_info where name='Default Trace File';
select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
注:摘录自网络