Trace file path

                                    Once we give command to generate trace oracle saves the trace in file in directory configured in USER_DUMP_DEST parameter. Once the tracing ends, we need to find the file according to particular naming convention. Basically, file name have instance name and OS PID through which trace file is generated. The following script returns the path to the trace file that the current session writes.

 
Query 1
=======
select name,value from v$parameter where name like '%user_dump_dest%'


Query 2
=======
select u_dump.value || '/' || instance.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 instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');