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.