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

獲取當前會話的trc檔名

相關文章