ORACLE 查看TRACE文件的两种方法

SQL> select d.value || b.bias || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
  2         p.spid || '.trc' trace_file_name
  3    from (select p.spid
  4            from sys.v$mystat m, sys.v$session s, sys.v$process p
  5           where m.statistic# = 1
  6             and s.sid = m.sid
  7             and p.addr = s.paddr) p,
  8         (select t.instance
  9            from sys.v$thread t, sys.v$parameter v
 10           where v.name = 'thread'
 11             and (v.value = 0 or t.thread# = to_number(v.value))) i,
 12         (select value from sys.v$parameter where name = 'user_dump_dest') d,
 13         (select DECODE(count(BANNER), 0, '/', '\') bias
 14            from v$version
 15           where upper(banner) like '%WINDOWS%') b;
 
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24916.trc
 
SQL> 
SQL> SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid ||
  2         '.trc' trace_file
  3    FROM (SELECT VALUE FROM v$parameter WHERE name = 'user_dump_dest') a,
  4         (SELECT SUBSTR(VALUE, -6, 1) symbol
  5            FROM v$parameter
  6           WHERE name = 'user_dump_dest') b,
  7         (SELECT instance_name FROM v$instance) c,
  8         (SELECT spid
  9            FROM v$session s, v$process p, v$mystat m
 10           WHERE s.paddr = p.addr
 11             AND s.sid = m.sid
 12             AND m.statistic# = 0) d;
 
TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24916.trc