SELECT fcr.request_id "Request ID",
p1.value "Trace Directory",
'*' || fcr.oracle_process_id || '*.trc' "Trace File format",
fcp.user_concurrent_program_name "Program"
FROM fnd_concurrent_requests fcr,
v$parameter p1,
fnd_concurrent_programs_tl fcp
WHERE p1.name = 'user_dump_dest'
AND fcp.language = 'US'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.request_id = &request_id;
更详细的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 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;
2、使用命令行(注意是在数据库的服务器上)
Find the trace files using command like below. See Note 290614.1 to find the location of the trace files which depends on the rdbms version.
--file number为Trace ID number
例子:ls -lt *56754*