Oracle EBS Application R12 - Concurrent Related Queries

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;

Oracle Support OTP Delay Issue & How MFA Can Help

Introduction

Recently, some Oracle users have faced delays in receiving OTPs (One-Time Passwords) while trying to log in to Oracle Support.

Due to these delays, users are unable to log in on time, which can disrupt access to critical support resources.

This post explains the issue and provides a workaround using Multi-Factor Authentication (MFA) to ensure OTPs are delivered reliably.

What’s Happening

Oracle Support requires OTPs for secure login. Occasionally, users experience:

• OTP emails not arriving within the expected time

• Delayed login access

• Multiple login attempts failing

Impact: Users cannot access Oracle Support for troubleshooting, patches, or service requests.

How Multi-Factor Authentication (MFA) Helps

If you have MFA enabled, you can use an alternative method to receive OTPs:

• Authenticator App (Google Authenticator, Microsoft Authenticator, or Oracle MFA App)

• Mobile Number (SMS OTP)

These alternatives help users log in even if email OTPs are delayed, ensuring uninterrupted access to Oracle Support.

Steps to Enable MFA for Oracle Support

1. Visit Oracle Cloud My Console:
   https://login-ext.identity.oraclecloud.com/ui/v1/myconsole?root=my-info&my-info=my_profile_security

2. Login using your Oracle account credentials.

3. Navigate to Security Settings → Multi-Factor Authentication (MFA).

4. Enable one or both options:

   • Mobile Number (SMS)

   • Authenticator App (TOTP)

5. Verify your setup by entering the OTP received via SMS or app.

6. Test login on Oracle Support to confirm MFA works as an alternative OTP delivery.

Best Practices

• Keep your mobile number up-to-date in Oracle profile.

• Use an authenticator app for faster OTP delivery.

• Share this guide with your team to prevent login delays.

• Always have an alternative method for OTP delivery to avoid access issues.

Conclusion

OTP delivery delays can block access to Oracle Support. By enabling MFA, users gain alternative methods to receive OTPs — ensuring uninterrupted login and access to support resources.

Follow this blog for more Oracle DBA, APPS DBA tips and troubleshooting guides.

Handling Locked Statistics in Gather Schema Statistics

Issue

During the execution of the Gather Schema Statistics concurrent program, the log file reported that certain tables had locked statistics, such as:

• WF_NOTIFICATION_OUT

• WF_NOTIFICATION_IN

This prevented the program from collecting fresh statistics.

---

Cause

The Gather Schema Statistics process failed because:

1. Some tables had their statistics locked, and

2. The FND_STATS_HIST table contained large volumes of data, impacting performance.

---

How to Identify Locked Statistics

Use the query below to find tables with locked statistics:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

This will list all objects where statistics are currently locked.

---

Solution

1. Unlock All Tables in a Schema

If many tables are locked, you can unlock the entire schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');

---

2. Unlock Statistics for a Specific Table

To unlock statistics for a single table, run:

exec dbms_stats.unlock_table_stats('table_owner', 'table_name');

Example:

exec dbms_stats.unlock_table_stats('APPLSYS', 'WF_NOTIFICATION_OUT');

RMAN Queries

In this post, I’ve shared a set of RMAN-related SQL queries that help you quickly check backup details, identify issues, and verify restore sessions— directly from the database views.

1. Find RMAN Complete Percentage

set linesize 110

set pagesize 300

SELECT sid, serial#, context, sofar, totalwork , opname,

round(sofar/totalwork*100,2) "Complete"

FROM v$session_longops

WHERE opname LIKE 'RMAN%' and totalwork<>0 and round(sofar/totalwork*100,2)<>100;

--More Details

col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"

col input_mbytes       for 99,999,990.00 justify right head "READ_MB"

col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"

col output_device_type for a10           justify left head "DEVICE"

col complete           for 990.00        justify right head "COMPLETE %" 

col compression        for 990.00        justify right head "COMPRESS|% ORIG"

col est_complete       for a20           head "ESTIMATED COMPLETION"

col recid              for 9999999       head "ID"

select recid

     , output_device_type

     , dbsize_mbytes

     , input_bytes/1024/1024 input_mbytes

     , output_bytes/1024/1024 output_mbytes

     , (output_bytes/input_bytes*100) compression

     , (mbytes_processed/dbsize_mbytes*100) complete

     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete

  from v$rman_status rs

     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 

 where status='RUNNING'

   and output_device_type is not null

/

2. Find RMAN session in Database

select b.sid, b.serial#, a.spid, b.client_info

from v$process a, v$session b

where a.addr=b.paddr and client_info like 'rman%';

3. Kill RMAN Sessions:

alter system kill session 'SID,SERIAL#' immediate;

alter system kill session 'SID,SERIAL#,@<Instance_id>' immediate;  -- In RAC

4. RMAN Restore Monitor:

SET LINE 132

SELECT opname, round(sofar/totalwork*100) "% Complete"

  FROM gv$session_longops

 WHERE opname LIKE 'RMAN%'

   AND totalwork != 0

   AND sofar <> totalwork

 ORDER BY 1;

5. RMAN Backup Details with Size:

set pages 2000 lines 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

ELAPSED_SECONDS/3600 hrs,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

6. History of RMAN Backup Details:

select

  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,

  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,

  (j.output_bytes/1024/1024/1024) output_Gbytes, j.status, j.input_type,

  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',

                                     3, 'Tuesday', 4, 'Wednesday',

                                     5, 'Thursday', 6, 'Friday',

                                     7, 'Saturday') dow,

  j.elapsed_seconds, j.time_taken_display,

  x.cf, x.df, x.i0, x.i1, x.l,

  ro.inst_id output_instance

from v$RMAN_BACKUP_JOB_DETAILS j

  left outer join (select

                     d.session_recid, d.session_stamp,

                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,

                     sum(case when d.controlfile_included = 'NO'

                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,

                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,

                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,

                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L

                   from

                     v$BACKUP_SET_DETAILS d

                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count

                   where s.input_file_scan_only = 'NO'

                   group by d.session_recid, d.session_stamp) x

    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp

  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id

                   from Gv$RMAN_OUTPUT o

                   group by o.session_recid, o.session_stamp)

    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp

where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS

order by j.start_time desc

/

TFA-00104 Cannot establish connection with TFA Server. Please check TFA Certificates

Error Message

While trying to collect the diagnostic or checking the tfa status, it gives an below error message

FA-00104: Cannot establish connection with TFA Server. Please check TFA Certificates

Solution:

Perform the following steps as the root user:

1. Check if the TFA service is running

ps -ef | grep tfa

2. Stop the TFA service

tfactl stop

3. Start the TFA service

tfactl start

TFA Commands

Introduction:

Oracle Trace File Analyzer (TFA) is a powerful diagnostic tool included with AHF (Autonomous Health Framework). It helps Oracle DBAs and system administrators collect, analyze, and manage diagnostic data for Oracle Database, Grid Infrastructure, and ASM environments — especially during performance or crash issues.

1. Check TFA Status

tfactl status
tfactl start
tfactl stop

2. Collect Diagnostics:

Connect as root user,
Be sure to set the date and time to 2 hours before and after the issue occurred in node-wise if it is RAC.

tfactl diagcollect -from "Jan/01/2025 00:00:00" -to "Jan/01/2025 02:00:00" -noclassify

--It will take some time to collect all the data.

com.sun.deploy.net.FailedDownloadException: Unable to load resource

Fixing JAR File Download Error After EBS R12.2 Clone

When working with Oracle E-Business Suite R12.2, it’s common to encounter unexpected issues after performing a clone. Recently, after a clone activity, a few users faced errors while trying to access form pages.

Issue

Users were unable to load forms and encountered the following error:

ExitException[3]com.sun.deploy.net.FailedDownloadException:
Unable to load resource: http://xxxxxxxx.yyyyyy.zzz:xxxx/OA_JAVA/oracle/apps/fnd/jar/fndbalishare.jar
at sun.plugin2.applet.JNLP2Manager.downloadResources(Unknown Source)
at sun.plugin2.applet.JNLP2Manager.prepareLaunchFile(Unknown Source)
at sun.plugin2.applet.JNLP2Manager.loadJarFiles(Unknown Source)
at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


Analysis

• Verified the Java exception list – it was working fine.

• Checked whether the application URL was added to the trusted sites – it was missing.

• Tested with SYSADMIN credentials in multiple browsers – the same issue persisted.

• Reviewed browser settings to confirm if insecure content was allowed – it was not.

Solution

The issue was resolved by making the following changes on the client side:

Add the application URL to trusted sites in the browser settings.
Allow insecure content for the application URL.
After applying these changes, users were able to access the forms without any errors.

Protocol Not Supported Error During OAuth IMAP Configuration

Issue:

While setting up OAuth for IMAP in Oracle E-Business Suite R12.2, you may encounter the following error during the IMAP connection test:

IMAP connection testing failed with: Protocol not supported

This typically occurs after following the Oracle documentation Doc ID 2884072.1 for OAuth configuration.

Solution:

1. To resolve this, explicitly instruct the JVM to use the Sun HTTPS handler and enforce TLS 1.2 for outbound connections by updating the application tier startup parameters.

-DUseSunHttpHandler=true -Dhttps.protocols=TLSv1.2

2. Apply the changes by stopping and starting the application services:

adstpall.sh & adstrtal.sh




Create a Custom Concurrent Manager in Oracle EBS R12.2

 Please follow the steps to create a custom concurrent Manager on the Oracle EBS R12.2 Application.

1. Stop the Concurrent manager services
2. Login to the application using sysadmin user.
3. Navigate to the Concurrent -> Manager -> Define
4. Create a custom manager as Custom Standard Manager.
- Set cache size as 50
- in Program Library -> Name: Library
- in Workshift -> mention 3 processes 
 - in Specialization rule -> include program's name as customizations and then save.
5. Exclude the concurrent program from the standard maanger
Concurrent-> Manager-> Define -> Standard Manager -> Specializations Rules -> Exclude all the above added concurrent programs.
6. Restart the concurrent Manager service
7. Activate the custom Concurrent Manager.

8. Submit the concurrent program and verify the same.

--Use the following query to identify which manager ran the concurrent request.

select distinct c.request_id, b.USER_CONCURRENT_QUEUE_NAME,e.USER_CONCURRENT_PROGRAM_NAME
from 
fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, 
fnd_concurrent_requests c,
fnd_concurrent_programs d,
FND_CONCURRENT_PROGRAMS_TL e
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    c.request_id in ('1234567','1234567','1234567');

Query to Monitor RMAN Restoration Progress in Oracle

 SET LINE 132

SELECT opname, round(sofar/totalwork*100) "% Complete"

  FROM v$session_longops

 WHERE opname LIKE 'RMAN%'

   AND totalwork != 0

   AND sofar <> totalwork

 ORDER BY 1;

Note: For RAC database use, gv$session_longops in the above query.