[管理脚本] 获得跟踪文件名

-- 专用服务器模式的连接下 -- user_dump_dest
-- 共享服务器模式的连接下 -- background_dump_dest
-- 9iR1 中 无需实例名

-- 可以设置 trace 标识符
go@DEMO> alter session set tracefile_identifier = 'Look_For_Me';
go@DEMO> select name, value from v$parameter where name = 'tracefile_identifier';
NAME                   VALUE
---------------------- -----------
tracefile_identifier   Look_For_Me

1 row selected.

-- trace
D:\oracle\product\10.2.0\admin\demo\udump\demo_ora_7660_look_for_me.trc

column trace format a80
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'
/

-- 或者

column trace format a80
SELECT
    d.VALUE ||
    '/' ||
    LOWER (RTRIM (i.INSTANCE, CHR (0))) ||
    '_ora_' || p.spid || '.trc' trace
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
/