Given a concurrent request id how do you find the trace file generated by this request id

Execute the
following query and give the request id when asked, that would display the
location and name of the trace file generated.

column traceid
format a8
column tracename
format a80
column
user_concurrent_program_name format a40
column execname
format a15
column enable_trace
format a12
set lines 80
set pages 22
set head off

SELECT ‘Request id:
‘||request_id ,
‘Trace id:
‘||oracle_Process_id,
‘Trace Flag:
‘||req.enable_trace,
‘Trace Name:
‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name:
‘||prog.user_concurrent_program_name,
‘File Name:
‘||execname.execution_file_name|| execname.subroutine_name ,
‘Status :
‘||decode(phase_code,’R’,’Running’)
||’-‘||decode(status_code,’R’,’Normal’),
‘SID Serial:
‘||ses.sid||’,’|| ses.serial#,
‘Module :
‘||ses.module
from
fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables
execname
where
req.request_id = &request
and
req.oracle_process_id=proc.spid(+)
and proc.addr =
ses.paddr(+)
and
dest.name=’user_dump_dest’
and
dbnm.name=’db_name’
and
req.concurrent_program_id = prog.concurrent_program_id
and
req.program_application_id = prog.application_id
and
prog.application_id = execname.application_id
and
prog.executable_id=execname.executable_id;
  • April 2, 2012 | 36 views
  • Comments