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));
注:摘录自网络