Trace details for a concurrent request along with the log and output file location details

 Script to get Trace details for a concurrent request along with the log file and output file location details

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

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 ;