oracle读取文件文件名不确定,Oracle数据库: 分析如何得到Oracle跟踪文件的文件名...

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

01 SELECT

02   d.VALUE

03   || '/'

04   || LOWER (RTRIM (i.INSTANCE, CHR (0)))

05   || '_ora_'

06   || p.spid

07   || '.trc' trace_name

08 FROM

09 (

10   SELECT

11     p.spid

12   FROM

13     v$mystat m, v$session s, v$process p

14   WHERE

15     m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr

16 ) p,

17 (

18   SELECT

19     t.INSTANCE

20   FROM

21     v$thread t, v$parameter v

22   WHERE

23     v.NAME = 'thread'

24   AND

25     ( v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE) )

26 ) i,

27 (

28   SELECT

29     VALUE

30   FROM v$parameter

31    WHERE NAME = 'user_dump_dest'

32 ) d;

1 TRACE_NAME

2 -----------------------------------------------------------------

3 /u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_8535.trc

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

01 create or replace function get_trace_name return varchar2 as

02     v_result varchar2(300);

03 begin

04   SELECT

05     d.VALUE

06     || '/'

07     || LOWER (RTRIM (i.INSTANCE, CHR (0)))

08     || '_ora_'

09     || p.spid

10     || '.trc' trace_name

11   INTO v_result

12   FROM

13   (

14     SELECT

15       p.spid

16     FROM

17       v$mystat m, v$session s, v$process p

18     WHERE

19       m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr

20   ) p,

21   (

22     SELECT

23       t.INSTANCE

24     FROM

25       v$thread t, v$parameter v

26     WHERE

27       v.NAME = 'thread'

28     AND

29       ( v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE) )

30   ) i,

31   (

32     SELECT

33       VALUE

34     FROM v$parameter

35      WHERE NAME = 'user_dump_dest'

36   ) d;

37   return v_result;

38 end get_trace_name;

建立公共同义词：

1 sys$logdw@logdw SQL> create or replace public synonym get_trace_name for get_trace_name;

2 sys$logdw@logdw SQL> grant execute on get_trace_name to public;

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

1 sys$logdw@logdw SQL> connect test/test

2 Connected.

3 test$logdw@logdw SQL> show user;

4 USER is "TEST"

5 test$logdw@logdw SQL> select get_trace_name() from dual;

6

7 GET_TRACE_NAME()

8 ------------------------------------------------------------------

9 /u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_8757.trc