Protected by Copyscape Web Copyright Protection Software

Search This Blog

Wednesday, May 29, 2013

How to find Trace File Names in Oracle apps based on request id?

Execute below query in toad or sqldeveloper the you will get Trace file Names.

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 = 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;