This blog consolidates a practical set of Oracle EBS concurrent request SQL queries that are extremely useful in a DBA’s daily support life. The queries help DBAs monitor concurrent manager activity, analyze program runtimes, identify long-running or blocked requests, trace SQL executed by concurrent programs, locate log and output files, verify scheduling and hold status, and safely troubleshoot problematic requests at the database level.
/*=============================================
1. concurrent request Executing SQL Query
===============================================
SELECT va.sql_text
,va.module
FROM APPS.fnd_concurrent_requests fcr,
V$SESSION vs,
V$SQLAREA va
WHERE fcr.oracle_session_id = vs.audsid
AND vs.sql_hash_value = va.hash_value
AND fcr.request_id = 59556211;
/*==============================================
2. Number of Concurrent Requests Ran Per Day
===============================================
select trunc(ACTUAL_START_DATE),count(1)
from applsys.fnd_concurrent_requests
where trunc(ACTUAL_START_DATE) <= trunc(sysdate)
group by trunc(ACTUAL_START_DATE)
order by trunc(ACTUAL_START_DATE) desc;
/*=============================================
3. Concurrent Program Runtime Details
===============================================
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
fu.user_name,
f.argument_text,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference,
p.concurrent_program_name,
f.phase_code,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f,
apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
and f.requested_by = fu.user_id
and pt.user_concurrent_program_name like '%Sales%Report%'
order by f.actual_start_date desc;
/*==============================================
4. Average Runtime for Specific Concurrent Program (All Status)
===============================================
SELECT
fcpt.user_concurrent_program_name AS program_name,
ROUND(AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60), 2) AS avg_runtime_minutes,
COUNT(*) AS total_runs
FROM apps.fnd_concurrent_requests fr,
apps.fnd_concurrent_programs_tl fcpt
WHERE fr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND fcpt.user_concurrent_program_name like '%Sales%Report%'
AND fr.actual_start_date IS NOT NULL
AND fr.actual_completion_date IS NOT NULL
AND fr.request_date > SYSDATE - 30
GROUP BY fcpt.user_concurrent_program_name
ORDER BY fcpt.user_concurrent_program_name;
/*===============================================
5. Average Runtime – Completed Normal Requests Only
================================================
SELECT
fcpt.user_concurrent_program_name AS program_name,
ROUND(AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60), 2) AS avg_runtime_minutes,
MIN((fr.actual_completion_date - fr.actual_start_date) * 24 * 60) AS min_runtime_minutes,
MAX((fr.actual_completion_date - fr.actual_start_date) * 24 * 60) AS max_runtime_minutes,
COUNT(*) AS total_runs
FROM apps.fnd_concurrent_requests fr,
apps.fnd_concurrent_programs_tl fcpt
WHERE fr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND fcpt.user_concurrent_program_name = 'Sales Report'
AND fr.phase_code = 'C'
AND fr.status_code = 'C'
AND fr.actual_start_date IS NOT NULL
AND fr.actual_completion_date IS NOT NULL
AND fr.request_date > SYSDATE - 30
GROUP BY fcpt.user_concurrent_program_name;
/*==============================================
6. Concurrent Programs with Average Runtime > 1 Hour
===============================================
SELECT
fcpt.user_concurrent_program_name AS program_name,
ROUND(AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60), 2) AS avg_runtime_minutes,
MIN((fr.actual_completion_date - fr.actual_start_date) * 24 * 60),
MAX((fr.actual_completion_date - fr.actual_start_date) * 24 * 60),
COUNT(*)
FROM apps.fnd_concurrent_requests fr,
apps.fnd_concurrent_programs_tl fcpt
WHERE fr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND fr.phase_code = 'C'
AND fr.status_code = 'C'
AND fr.actual_start_date IS NOT NULL
AND fr.actual_completion_date IS NOT NULL
AND fr.request_date > SYSDATE - 30
GROUP BY fcpt.user_concurrent_program_name
HAVING AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60) > 60
ORDER BY avg_runtime_minutes DESC;
/*==============================================
7. Output File Format of Concurrent Program
================================================
SELECT cp.concurrent_program_name,
cp.user_concurrent_program_name,
cp.output_file_type
FROM fnd_concurrent_programs_vl cp
WHERE cp.user_concurrent_program_name like '%Sales%Report%';
/*==============================================
8. Pending / Standby Concurrent Programs
================================================
SELECT f.request_id,
pt.user_concurrent_program_name,
fu.user_name,
f.argument_text,
f.phase_code,
f.status_code
FROM apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f,
apps.fnd_user fu
WHERE f.concurrent_program_id = pt.concurrent_program_id
AND pt.language = USERENV('Lang')
AND f.actual_start_date IS NULL
AND f.requested_by = fu.user_id
AND f.phase_code = 'P'
AND pt.user_concurrent_program_name like '%Sales%Report%';
/*=============================================
9. Find SID & PID from Concurrent Request ID
==============================================
SELECT C.SID, C.SERIAL#, A.PID
FROM GV$PROCESS A, FND_CONCURRENT_REQUESTS B, GV$SESSION C
WHERE B.ORACLE_PROCESS_ID = A.SPID
AND B.REQUEST_ID = '5324543211'
AND A.ADDR = C.PADDR;
/*=============================================
10. Terminate Concurrent Program (DB Level – Use Carefully)
===============================================
--UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE request_ID = 12334435;
/*=============================================
11. Currently Running Concurrent Programs
===============================================
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60),2) Process_time,
a.request_id,
a.actual_start_date,
a.phase_code,
a.status_code
FROM apps.fnd_concurrent_requests a,
apps.FND_CONCURRENT_PROGRAMS_TL c
WHERE a.concurrent_program_id = c.concurrent_program_id
AND status_code = 'R'
ORDER BY Process_time DESC;
/*=============================================
12. Log and Output File Locations
===============================================
select logfile_name from fnd_concurrent_requests where request_id = <request_id>;
select outfile_name from fnd_concurrent_requests where request_id = <request_id>;
/*=============================================
13. Internal Concurrent Manager Log File Location
===============================================
SELECT 'ICM_LOG_NAME=' || fcp.logfile_name
FROM fnd_concurrent_processes fcp,
fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';
/*=============================================14. Find responsibility name from concurrent program name
===============================================
SELECT frt.responsibility_name, frg.request_group_name,
frgu.request_unit_type,frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND frt.LANGUAGE = USERENV('LANG')
AND fcpt.LANGUAGE = USERENV('LANG')
AND fcpt.user_concurrent_program_name = 'conc_prog_name'
ORDER BY 1,2,3,4;
/*==============================================
15. Long Running Concurrent Requests More than 30 Minutes
================================================
select distinct qt.user_concurrent_queue_name "Queue Name"
,c2.user_concurrent_program_name "Program Name"
,a.request_id "Request Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
,FLVP.meaning "Phase"
,FLVS.meaning "Status"
,a.argument_text "Arguments"
--,b.os_process_id "OS_PID"
,vs.inst_id
,vs.sid "SID"
,vs.serial# "Serial#"
,vp.spid "SPID",vs.sql_id "SQL"
,vs.event "EVENT"
,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "TimeSpent"
,u.user_name "USERNAME"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,applsys.fnd_concurrent_queues_tl qt
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.FND_LOOKUP_VALUES FLVP
,APPLSYS.FND_LOOKUP_VALUES FLVS
,APPLSYS.FND_USER u
,gv$session vs
,gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and u.user_id=a.requested_by
and a.phase_code=FLVP.Lookup_Code
and FLVP.Lookup_Type='CP_PHASE_CODE'
and FLVP.language='US'
and a.status_code=FLVS.Lookup_Code
and FLVS.Lookup_Type='CP_STATUS_CODE'
and FLVS.language='US'
and FLVS.view_application_id=0
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and q.application_id = qt.application_id
and qt.language='US'
and q.concurrent_queue_id = qt.concurrent_queue_id
and c2.language = 'US'
--and vs.process (+) = b.os_process_id
and a.oracle_process_id = vp.spid
and vs.paddr = vp.addr (+)
and a.status_code='R'
and c2.USER_CONCURRENT_PROGRAM_NAME in ('Salres Report')
and vs.inst_id=vp.inst_id
and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > 30
order by 11 desc;
/*================================================
16. Find the completed Concurrent program Name using printer name
==================================================
select * from fnd_concurrent_requests where printer like 'PRINTER_NAME';
/*================================================
17. Concurrent completed Error with start date:
==================================================
select request_id, actual_start_date,actual_completion_date
,round((actual_completion_date - actual_start_date) * 24 * 60,2) time_IN_MINUTE
,phase_code, status_code
,fcp.user_concurrent_program_name
,(SELECT USER_NAME FROM FND_USER WHERE USER_ID = REQUESTED_BY) USER_NAME
from fnd_concurrent_Requests fcr
,fnd_concurrent_programs_vl fcp
where actual_start_date between trunc(sysdate - 1) and '05-JUL-2023 17:00:00'
and fcr.concurrent_program_id = fcp.concurrent_program_id
and status_code in ( 'X','E')
AND round((actual_completion_date - actual_start_date) * 24 * 60,2) > 10
order by ACTUAL_START_DATE ;
/*===============================================
18. Concurrent Program on hold status
================================================
set lines 900 pages 300
set heading on
set feedback on
set markup html on
SELECT a.request_id, program,
to_char(a.requested_start_date,'DD-MON-YYYY HH24:MI:SS') req_start_date,
to_char(a.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS')LAST_UPDATED_DATE,a.requestor,b.USER_NAME LAST_UPDATED_BY,
decode(a.phase_code,
'P','Pending',
'Running') Phase_code,
decode(a.status_code,
'R', 'Normal',
'W', 'Paused',
'Q', 'Standby',
'I', 'Scheduled') Status_code,
decode(a.hold_flag,'Y','Yes','No') Hold_flag
FROM fnd_conc_req_summary_v a,fnd_user b
WHERE trunc(a.requested_start_date) >= '31-DEC-2025'
and hold_flag='Y'
and a.status_code not in ('D')
and a.last_updated_by=b.user_id
order by 3 desc;
/*===============================================
19. Output Post Processor (OPP) Log File Location from Request ID
================================================
SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id =23245346;
/*=================================================
20. Find SQL text from Concurrent Request ID
===================================================
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
E.SQL_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS A,
APPS.FND_CONCURRENT_PROCESSES B,
V$PROCESS C,
V$SESSION D,
V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND C.PID = B.ORACLE_PROCESS_ID
AND B.SESSION_ID = D.AUDSID
AND D.SQL_ADDRESS = E.ADDRESS
AND A.REQUEST_ID = &REQUEST_ID;
/*===============================================
21. Fidn the Standby Concurrent log and output node name & update
=================================================
select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;
--update apps.fnd_concurrent_requests set logfile_node_name = 'TESTAP’, outfile_node_name = 'TESTAP' where logfile_node_name = 'PRODAP' and outfile_node_name = 'PRODAP';
select distinct file_node_name from fnd_conc_req_outputs;
--update apps.fnd_conc_req_outputs set file_node_name='TESTAP' where file_node_name='PRODAP';
/*===============================================
22. Concurrent program running more than 10 mins
=================================================
SELECT f.request_id,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.status_code
FROM apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE
f.phase_code ='R'
and pt.language='US'
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND ROUND (((SYSDATE - actual_start_date)*60*24), 2) > 10
AND pt.user_concurrent_program_name='Sales Report'
AND f.actual_start_date IS NOT NULL;
/*===============================================
23. Pending concurrent programs
=================================================
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,fu.user_name,f.argument_text,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
--decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.phase_code,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f,
apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is null
and f.requested_by = fu.user_id
--and fu.user_name='XXXX'
and pt.user_concurrent_program_name like '%%Sales%Report%%'
--and f.argument_text like '%'
order by
f.actual_start_date desc;
/*=============================================
24. concurrent requests that ran for over X minutes (more than)
==============================================
SELECT f.request_id
, TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) runtime
, f.actual_start_date asd
, f.actual_completion_date acd
, DECODE(p.concurrent_program_name,
'ALECDC',p.concurrent_program_name||'['||f.description||']'
,p.concurrent_program_name) cpn
, pt.user_concurrent_program_name ucpn
, f.phase_code
, f.status_code
, f.logfile_name
, f.argument_text
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) > NVL('&minutes',60)
and f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
ORDER by f.actual_completion_date-f.actual_start_date desc;
/*=============================================
25. Find the trace file enabled concurrent program details
===============================================
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 apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.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;
/*==============================================
26. Find scheduled concurrent program for specific request
================================================
SELECT fcpl.user_concurrent_program_name AS concurrent_program_name,
fcp.concurrent_program_name AS short_name,
fcr.request_id,
fcr.phase_code,
fcr.status_code,
fcr.requested_start_date,
fcr.actual_start_date,
fcr.actual_completion_date,
fr.responsibility_name,
fu.user_name
FROM fnd_concurrent_requests fcr
JOIN fnd_concurrent_programs_tl fcpl
ON fcr.concurrent_program_id = fcpl.concurrent_program_id
JOIN fnd_concurrent_programs fcp
ON fcr.concurrent_program_id = fcp.concurrent_program_id
JOIN fnd_responsibility_tl fr
ON fcr.responsibility_id = fr.responsibility_id
JOIN fnd_user fu
ON fcr.requested_by = fu.user_id
WHERE fcpl.user_concurrent_program_name = 'Gather Schema Statistics'
AND fcr.phase_code = 'P' -- Pending
AND fcr.status_code = 'Q' -- Scheduled
AND fcpl.language = 'US' -- Language preference
ORDER BY fcr.requested_start_date;
/*==============================================
27. pending/scheduled normal status
================================================
SELECT
f.request_id, pt.user_concurrent_program_name user_conc_program_name, fu.user_name, f.argument_text,f.org_id, f.REQUESTED_START_DATE, p.concurrent_program_name concurrent_program_name, f.phase_code, f.status_code
from
apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f,
apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
--and f.actual_start_date is not null
and f.requested_by = fu.user_id
and f.Phase_code ='P'
and f.STATUS_CODE = 'I'
--and f.org_id is not null
order by pt.user_concurrent_program_name asc;
/*===============================================
28. find Concurrent program running time between 23:00 to 3 AM
=================================================
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,fu.user_name,f.argument_text,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
--decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.phase_code,
f.status_code,
f.actual_start_date
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f,
apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
and f.requested_by = fu.user_id
--and fu.user_name='XXXXX'
--and pt.user_concurrent_program_name like 'Gather%'
--and f.argument_text like '%UAE%'
and f.actual_start_date >= sysdate- 2
and TO_NUMBER(to_char(f.actual_start_date,'HH24')) IN (23,0,1,2,3)
order by
f.actual_start_date desc;
/*================================================
29. Find which concurrent manager ran a specific concurrent request.
==================================================
select b.USER_CONCURRENT_QUEUE_NAME
from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = &request_id;
--and c.request_id in ('121196','125189','1251182','1251161','125157','125154','125250');
/*===============================================
30. concurrent Program locked session status & kill query:
=================================================
select 'alter system kill session ''' ||s.sid||','||s.serial#||''||',@' || s.inst_id || '''' || ' immediate; ' from gv$session s where sql_id='fnpyvpk41nd5s';
/*===============================================
31. Find the concurrent program completed more than 1 hour
================================================
SELECT *
FROM (
SELECT
frt.responsibility_name,
fcr.request_id,
fcp.user_concurrent_program_name AS program_name,
fu.user_name,
fcr.actual_start_date,
fcr.actual_completion_date,
ROUND(((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60), 2) AS duration_minutes,
ROW_NUMBER() OVER (PARTITION BY frt.responsibility_name ORDER BY fcr.actual_start_date DESC) AS rn
FROM
fnd_concurrent_requests fcr
JOIN fnd_user fu ON fcr.requested_by = fu.user_id
JOIN fnd_concurrent_programs_tl fcp ON fcr.concurrent_program_id = fcp.concurrent_program_id
JOIN fnd_responsibility_tl frt ON fcr.responsibility_id = frt.responsibility_id
WHERE
fcr.phase_code = 'C' -- Completed
AND fcr.actual_start_date IS NOT NULL
AND fcr.actual_completion_date IS NOT NULL
AND ((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60) > 60
AND fcp.LANGUAGE = 'US'
AND frt.LANGUAGE = 'US'
)
WHERE rn = 1
ORDER BY responsibility_name;
/*==============================================
32. Query to get the count of concurrent program for last one day
================================================
SELECT
e.user_concurrent_program_name,
COUNT(DISTINCT c.request_id) AS request_count
FROM
fnd_concurrent_processes a,
fnd_concurrent_queues_vl b,
fnd_concurrent_requests c,
fnd_concurrent_programs d,
fnd_concurrent_programs_tl e,
fnd_user f
WHERE
a.concurrent_queue_id = b.concurrent_queue_id
AND a.concurrent_process_id = c.controlling_manager
AND c.concurrent_program_id = d.concurrent_program_id
AND d.concurrent_program_id = e.concurrent_program_id
AND b.user_concurrent_queue_name = 'Sales Report'
AND c.requested_by = f.user_id
AND e.language = 'US'
AND TRUNC(c.request_date) >= TRUNC(SYSDATE - 1)
GROUP BY
e.user_concurrent_program_name
ORDER BY
request_count DESC;
/*==============================================
33. Printer related queries to find the load of the printer using size of concurrent program.
===============================================
SELECT
r.request_id,
p.user_concurrent_program_name,
r.argument_text,
r.actual_start_date,
r.actual_completion_date,
r.status_code,
r.completion_text,
o.file_name,
o.file_size / 1024 AS file_size_kb
FROM
fnd_concurrent_requests r
JOIN
fnd_concurrent_programs_tl p
ON r.concurrent_program_id = p.concurrent_program_id
AND p.language = 'US'
LEFT JOIN
fnd_conc_req_outputs o
ON r.request_id = o.concurrent_request_id
WHERE
p.user_concurrent_program_name = 'Consumer Tax Invoice/DN'
--AND r.actual_start_date >= SYSDATE - 7 -- Last 7 days (adjust as needed)
AND TRUNC(r.actual_start_date) = TO_DATE('05-JUL-2025','DD-MON-YYYY')
ORDER BY
r.actual_start_date DESC;
SELECT
TRUNC(r.actual_start_date) AS run_date,
COUNT(*) AS request_count,
ROUND(SUM(o.file_size) / (1024 * 1024), 2) AS total_file_size_mb
FROM
fnd_concurrent_requests r
JOIN
fnd_concurrent_programs_tl p
ON r.concurrent_program_id = p.concurrent_program_id
AND p.language = 'US'
JOIN
fnd_conc_req_outputs o
ON r.request_id = o.concurrent_request_id
WHERE
p.user_concurrent_program_name like '%%Invoice%' -- Replace with your program
AND r.actual_start_date >= TRUNC(SYSDATE - 7) -- Last 7 days (adjust if needed)
AND r.phase_code = 'C' -- Completed
AND r.status_code = 'C' -- Normal
GROUP BY
TRUNC(r.actual_start_date)
ORDER BY
run_date DESC;
