ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 获取当前会话的trc文件名

获取当前会话的trc文件名

原创 Linux操作系统 作者:ljm0211 时间:2012-06-20 15:20:37 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-733396/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    436869