转自:如何得到跟踪文件的文件名
跟踪文件非常有助于我们分析问题,跟踪文件的文件名可以用以下查询得到:
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