SQL Query to list running concurrent requests

col user_name format a20 word_wrapped
col ProgName format a25 word_wrapped
col requestId format 99999999
col StartDate format a20 word_Wrapped
col OS_PROCESS_ID format a6
col ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
set lines 200

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,’DD-MON-YYYY HH24:MI:SS’) StartDate,
request_id RequestId,
(sysdate – actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = ‘US’
and fcr.phase_code = ‘R’
and fcr.status_code = ‘R’
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC
/

Replace v$session with gv$session if the database is RAC.

Click here to extract the sql being executed by a concurrent program

cheers,
OraclePitStop