方法1：11g以前的方法
SQL> select d.value
2 ||'/'
3 ||lower(rtrim(i.instance,chr(0)))
4 ||'_ora_'
5 ||p.spid
6 ||'.trc' trace_file_name
7 from (select p.spid
8 from v$mystat m,v$session s,v$process p
9 where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
10 (select t.instance from v$thread t,v$parameter v
11 where v.name='thread' and (v.value=0 or t.thread#=to_number(v.value))) i,
12 (select value from v$parameter where name='user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/earth/earth/trace/earth_ora_10070.trc
方法2 11g：
SQL> select spid,tracefile from V$process where addr in(select paddr from v$session where sid=sys_context('userenv','sid'));
SPID TRACEFILE
------------------------ --------------------------------------------------------------------------------
10070 /u01/app/oracle/diag/rdbms/earth/earth/trace/earth_ora_10070.trc
方法3 11g：
SQL> select * from v$diag_info where name='Default Trace File';
INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------------------------------------
1 Default Trace File /u01/app/oracle/diag/rdbms/earth/earth/trace/earth_ora_10070.trc
------------------------from puber carcase
