--网上的一些解答:
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
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 的最短,我认为最好。