SELECT    d.VALUE
         || '/'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'
        || p.spid
         || '.trc' trace_file_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_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/oa/udump/oa2_ora_459246.trc

以上是eygle给出的sql语句，我解释一下：

1.SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') 可以得到路径名。

2.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 可以得到当前会话的操作系统进程ID，取m.statistic# = 1只是为了取v$mystat的一行记录的sid字段值，没有特殊的意义。

3.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))) 该语句中，如果是rac，则v中的value=1,2,3..，v$thread相应有多个记录，如果是单机库，则value=0，v$thread只有一条记录，因此用了value=0 or ...的判断条件。

其实为了取instance_name，没有必要这么复杂了，比如可以用下面的语句代替：SELECT instance_name FROM v$instance 参考v$thread,v$intance,gv$istance的输出: SQL> select value from v$parameter where name='user_dump_dest';

VALUE
--------------------------------------------------------------------------------
/opt/oracle/admin/oa/udump

SQL> select * from v$mystat where statistic#=1;

SID STATISTIC#      VALUE
---------- ---------- ----------
      1408          1          1 SQL> select * from v$thread;

   THREAD# STATUS ENABLED      GROUPS INSTANCE OPEN_TIME
---------- ------ -------- ---------- --------- -----------
         1 OPEN   PUBLIC            3 oa1       2007-12-17
         2 OPEN   PUBLIC            3 oa2       2007-12-25

SQL> select * from v$parameter where name='thread';

       NUM NAME                                            TYPE VALUE
---------- ----------------------------------------- ---------- --------
       621 thread                                             3 2

SQL> select * from gv$instance;

INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION     STARTUP_TIME STATUS
------- --------------- ------------- --------- ----------- ------------ ------
      2               2 oa2           Db02      10.2.0.3.0 2007-12-25 1 OPEN
      1               1 oa1           Db01      10.2.0.3.0 2007-12-17 1 OPEN

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME VERSION           STARTUP_TIME STATUS
--------------- ---------------- ---------- ----------------- ------------ -------
              2 oa2              Db02       10.2.0.3.0        2007-12-25 1 OPEN

ITPub博客

获取当前会话的trc文件名

最新文章