Track and Trace E-Business Suite Concurrent Request - EBS Logs, O/S Process IDs, Locks

select /*+ ordered */
fcp.user_concurrent_program_name
, fcr.request_id
, round(24*60*( sysdate - actual_start_date )) elapsed
, fu.user_name
, fcr.oracle_process_id
, sess.sid
, sess.serial#
, inst.inst_name
, sa.sql_text
, cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
, cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from apps.fnd_concurrent_requests fcr
, apps.fnd_concurrent_programs_tl fcp
, apps.fnd_concurrent_processes cp
, apps.fnd_user fu
, gv$process pro
, gv$session sess
, gv$sqlarea sa
, sys.v_$active_instances inst
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcp.application_id = fcr.program_application_id
and fcr.controlling_manager = cp.concurrent_process_id
and fcr.requested_by = fu.user_id (+)
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and sess.sql_address = sa.address (+)
and sess.sql_hash_value = sa.hash_value (+)
and sess.inst_id = inst.inst_number (+)
and fcr.phase_code = 'R' /* only running requests */
;
From the above we can see key information:
The running Concurrent Request Program Name and Request_ID
The database node operating system process id (OSPID) so we can monitor usage via top / iostat / vmstat
The SID / Serial in case we want to kill the session via alter system kill session '130,29699';
The instance name the database session is running on in a RAC environment
The currently running SQL text
The temporary files where concurrent request log/out is being written to via utl_file while running. These files are copied over to the Concurrent Tier $APPLCSF/$APPLOUT and $APPLLOG after completion of the request.