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