This script will identify the trace file on the database server from a Request ID if it generates a trace file. It also gives the session information if the session is still running. Usually if the trace option is turned on, a trace file is created by the Request.
SQL> 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 apps.fnd_concurrent_requests req, v$session ses,
v$process proc, v$parameter dest, v$parameter dbnm,
apps.fnd_concurrent_programs_vl prog, apps.fnd_executables execname
where req.request_id = 31414905
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 ;
Similar information can be got from two simple queries:
SQL> select value from v$parameter where name = 'user_dump_dest';
VALUE
----------------------------------------------------------------------
/path/to/udump
SQL> select oracle_process_id, enable_trace from fnd_concurrent_requests where request_id = 5589516;
ORACLE_PROCESS_ID ENABLE_TRACE
-------------------------- ------------------
11662 N
