Post date: Oct 30, 2013 11:28:10 AM
--Script to get Trace details for a concurrent request along with the log file and output file location details
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 , TO_CHAR(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS') Request_start_date ,
TO_CHAR(req.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') Request_completion_date ,
req.logfile_name ,
req.outfile_name ,
req.argument_text parameters_passed
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 = :p_request_id
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 ;