[转]如何得到Oracle跟踪文件的文件名

转自：如何得到跟踪文件的文件名

跟踪文件非常有助于我们分析问题，跟踪文件的文件名可以用以下查询得到：

SELECT 
  d.VALUE
  || '/'
  || LOWER (RTRIM (i.INSTANCE, CHR (0)))
  || '_ora_'
  || p.spid
  || '.trc' trace_name
FROM 
(
  SELECT 
    p.spid
  FROM 
    v$mystat m, v$session s, v$process p
  WHERE 
    m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr
) p,
(
  SELECT 
    t.INSTANCE
  FROM 
    v$thread t, v$parameter v
  WHERE 
    v.NAME = 'thread'
  AND 
    ( v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE) )
) i,
(
  SELECT 
    VALUE
  FROM v$parameter
   WHERE NAME = 'user_dump_dest'
) d; 

TRACE_NAME
-----------------------------------------------------------------
/u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_8535.trc

但是每次都要默写如此常常的SQL语句非常痛苦，我们可以对其包装成函数，然后让public都可以执行：

create or replace function get_trace_name return varchar2 as
	v_result varchar2(300); 
begin
  SELECT 
    d.VALUE
    || '/'
    || LOWER (RTRIM (i.INSTANCE, CHR (0)))
    || '_ora_'
    || p.spid
    || '.trc' trace_name
  INTO v_result
  FROM 
  (
    SELECT 
      p.spid
    FROM 
      v$mystat m, v$session s, v$process p
    WHERE 
      m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr
  ) p,
  (
    SELECT 
      t.INSTANCE
    FROM 
      v$thread t, v$parameter v
    WHERE 
      v.NAME = 'thread'
    AND 
      ( v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE) )
  ) i,
  (
    SELECT 
      VALUE
    FROM v$parameter
     WHERE NAME = 'user_dump_dest'
  ) d; 
  return v_result; 
end get_trace_name;

建立公共同义词：

sys$logdw@logdw SQL> create or replace public synonym get_trace_name for get_trace_name; 
sys$logdw@logdw SQL> grant execute on get_trace_name to public;

现在普通用户也可以使用了：

sys$logdw@logdw SQL> connect test/test
Connected.
test$logdw@logdw SQL> show user; 
USER is "TEST"
test$logdw@logdw SQL> select get_trace_name() from dual; 

GET_TRACE_NAME()
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_8757.trc