方法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