ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 用户Trace 文件路径

Oracle 用户Trace 文件路径

原创 Linux操作系统 作者:myhuaer 时间:2009-05-20 18:49:57 0 删除 编辑

用户SQL Trace 的文件路径。

下面载录几个和我写的一个。

--网上的一些解答:

SELECT c.VALUE || '/' || d.instance_name || '_ora_' || a.spid || '.trc' TRACE
  FROM v$process a, v$session b, v$parameter c, v$instance d
 WHERE a.addr = b.paddr
   AND b.audsid = USERENV ('sessionid')
   AND c.NAME = 'user_dump_dest'

/disk/oracle/admin/study/udump/study_ora_13015.trc


eygle.com said:

SELECT    d.VALUE
       || '/'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'
       || p.spid
       || '.trc' trace_file_name
  FROM (SELECT p.spid
          FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p
         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM SYS.v$thread t, SYS.v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
       (SELECT VALUE
          FROM SYS.v$parameter
         WHERE NAME = 'user_dump_dest') d

/disk/oracle/admin/study/udump/study_ora_13015.trc

 

René Nyffenegger said:


select
  u_dump.value   || '/'     ||
  db_name.value  || '_ora_' ||
  v$process.spid ||
  nvl2(v$process.traceid,  '_' || v$process.traceid, null )
  || '.trc'  "Trace File"
from
             v$parameter u_dump
  cross join v$parameter db_name
  cross join v$process
        join v$session
          on v$process.addr = v$session.paddr
where
 u_dump.name   = 'user_dump_dest' and
 db_name.name  = 'db_name'        and
 v$session.audsid=sys_context('userenv','sessionid');

/disk/oracle/admin/study/udump/study_ora_13015.trc

Tom kytes:

select c.value || '/' || instance || '_ora_' ||
       ltrim(to_char(a.spid,'fm99999')) || '.trc'
  from v$process a, v$session b, v$parameter c, v$thread c
 where a.addr = b.paddr
   and b.audsid = userenv('sessionid')
   and c.name = 'user_dump_dest'
  
/disk/oracle/admin/study/udump/study_ora_13015.trc


Lis Li said:
 
    SELECT p1.value||'/'||p2.value||'_ora_'||p.spid||'.trc' filename
    FROM
        v$process p,
        v$session s,
        v$parameter p1,
        v$parameter p2
    WHERE p1.name = 'user_dump_dest'
    AND p2.name = 'db_name'
    AND p.addr = s.paddr
    AND s.audsid = USERENV ('SESSIONID');

/disk/oracle/admin/study/udump/study_ora_13015.trc

从SQL 长度上来看Tom 的最短,我认为最好。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/34596/viewspace-600589/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORA-01555 say u again
请登录后发表评论 登录
全部评论

注册时间:2016-09-11

  • 博文量
    211
  • 访问量
    627899