10g:
/* 11g依然有效 */

SELECTd.VALUE
||'/'
||LOWER(RTRIM(i.instance,CHR(0)))
||'_ora_'
||p.spid
||'.trc'
trace_file_name
FROM(SELECTp.spid
FROMsys.v$mystatm,sys.v$sessions,sys.v$processp
WHEREm.statistic#=1ANDs.sid=m.sidANDp.addr=s.paddr)p,(SELECTt.instance
FROMsys.v$threadt,sys.v$parameterv
WHEREv.name='thread'
AND(v.VALUE=0ORt.thread#=TO_NUMBER(v.VALUE)))i,(SELECTVALUE
FROMsys.v$parameter
WHEREname='user_dump_dest')d;

11g:

selectvaluefromv$diag_infowherename='DefaultTraceFile';

selecttracefilefromv$processwhereaddr=(selectpaddrfromv$sessionwheresid=(selectdistinctsidfromv$mystat));

注：摘录自网络