For UNIX
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
For Windows
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
