Oracle E-Business Suite Apps DBA – Essential SQL Queries and Administration Scripts

This technical reference consolidates useful Oracle E-Business Suite administration and troubleshooting queries commonly required by Apps DBAs. The scripts cover database sessions, Concurrent Processing, Workflow Mailer, patching, ADOP, profile options, application users, tablespaces, performance troubleshooting and other day-to-day administration activities.

Important: Validate every command in a non-production environment before using it in Production. Queries that modify EBS application tables, terminate operating system processes, alter ADOP metadata or expose credentials should not be executed as routine troubleshooting procedures.

Contents

Blocking Sessions and Locks
Concurrent Processing
Long Running Requests
Scheduled and Pending Requests
Output Post Processor
Workflow Mailer
Profile Options
Installed Products and Patch Levels
EBS Patching and ADOP
Database Sessions
Tablespace and Segment Monitoring
Performance Diagnostics
Users and Responsibilities
Tracing and Diagnostics
Production Safety Notes

1. Blocking Sessions and Locks

Blocking-session analysis is one of the most common database-level troubleshooting activities for an Oracle E-Business Suite DBA.

Find Blocking Sessions

set lines 130
set pages 200

column module format a20
column program format a20
column username format a15

select s.sid,
       s.serial#,
       s.status,
       p.spid,
       s.module,
       s.action,
       s.program
from   v$session s,
       v$process p
where  s.sid in
       (select session_id
          from dba_locks
         where blocking_others = 'Blocking')
and    s.paddr = p.addr;

Do not terminate a database session solely because it appears as a blocker. First identify the application process, transaction, request, user and business impact.

Find Locked Objects

column object_name format a40

SELECT a.object_id,
       a.session_id,
       b.object_name
FROM   v$locked_object a,
       dba_objects b
WHERE  a.object_id = b.object_id
AND    b.owner = 'AP';

2. Concurrent Processing

Concurrent Processing is a core component of Oracle E-Business Suite. The following queries are useful when investigating running requests, pending requests, manager capacity and request execution.

Running Concurrent Requests

SELECT DISTINCT
       c.user_concurrent_program_name,
       ROUND(((SYSDATE-a.actual_start_date)*24*60),2)
          AS process_time_minutes,
       a.request_id,
       a.parent_request_id,
       a.request_date,
       a.actual_start_date,
       a.actual_completion_date,
       d.user_name,
       a.phase_code,
       a.status_code,
       a.argument_text,
       a.priority
FROM   apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_programs b,
       apps.fnd_concurrent_programs_tl c,
       apps.fnd_user d
WHERE  a.concurrent_program_id = b.concurrent_program_id
AND    b.concurrent_program_id = c.concurrent_program_id
AND    a.requested_by = d.user_id
AND    a.status_code = 'R'
ORDER BY process_time_minutes DESC;

Pending Requests Waiting for Managers

set lines 130

col user_concurrent_queue_name format a39

SELECT b.user_concurrent_queue_name,
       COUNT(*)
FROM   apps.fnd_concurrent_worker_requests a,
       apps.fnd_concurrent_queues_vl b
WHERE  a.phase_code = 'P'
AND    a.hold_flag != 'Y'
AND    a.requested_start_date <= SYSDATE
AND    a.concurrent_queue_id != 1003
AND    a.concurrent_queue_id = b.concurrent_queue_id
GROUP BY b.user_concurrent_queue_name,
         a.status_code;

3. Long Running Concurrent Requests

The following example identifies requests that have been running for more than four hours.

set lines 130

column start_time format a15
column user_concurrent_program_name format a40

SELECT b.request_id,
       a.user_concurrent_program_name,
       b.phase_code,
       (SYSDATE-b.actual_start_date)*24 AS running_hours,
       TO_CHAR(b.request_date,
               'MM/DD/YYYY HH24:MI') AS request_date,
       TO_CHAR(b.actual_start_date,
               'MM/DD/YYYY HH24:MI') AS start_time
FROM   apps.fnd_concurrent_programs_vl a,
       apps.fnd_concurrent_requests b
WHERE  a.concurrent_program_id = b.concurrent_program_id
AND    a.application_id = b.program_application_id
AND    b.status_code = 'R'
AND    b.phase_code = 'R'
AND    ((SYSDATE-b.actual_start_date)*24) > 4;

4. Scheduled and Pending Requests

Count Scheduled Requests

SELECT 'Scheduled requests:' schedt,
       COUNT(*) schedcnt
FROM   fnd_concurrent_requests
WHERE  (requested_start_date > SYSDATE
        OR status_code = 'P')
AND    phase_code = 'P';

Requests on Hold

SELECT 'Requests on hold:' schedt,
       COUNT(*) schedcnt
FROM   fnd_concurrent_requests
WHERE  hold_flag = 'Y'
AND    phase_code = 'P';

5. Output Post Processor

Find OPP Log for a Concurrent Request

SELECT fcpa.concurrent_request_id req_id,
       fcp.node_name,
       fcp.logfile_name
FROM   fnd_conc_pp_actions fcpa,
       fnd_concurrent_processes fcp
WHERE  fcpa.processor_id = fcp.concurrent_process_id
AND    fcpa.action_type = 6
AND    fcpa.concurrent_request_id = :P_REQUEST_ID;

6. Workflow Mailer

Workflow Mailer Configuration

set lines 130
set pages 200

col value format a30

SELECT p.parameter_id,
       p.parameter_name,
       v.parameter_value value
FROM   apps.fnd_svc_comp_param_vals_v v,
       apps.fnd_svc_comp_params_b p,
       apps.fnd_svc_components c
WHERE  c.component_type = 'WF_MAILER'
AND    v.component_id = c.component_id
AND    v.parameter_id = p.parameter_id
AND    p.parameter_name IN
       ('OUTBOUND_SERVER',
        'INBOUND_SERVER',
        'ACCOUNT',
        'FROM',
        'NODENAME',
        'REPLYTO',
        'DISCARD',
        'PROCESS',
        'INBOX')
ORDER BY p.parameter_name;

7. Profile Option Auditing

Profile-option history can be useful when troubleshooting a problem that started after an application configuration change.

SELECT tl.user_profile_option_name "Profile Option",
       val.profile_option_value "Value",
       val.last_update_date "Set On",
       usr.user_name "Set By"
FROM   fnd_profile_options opt,
       fnd_profile_option_values val,
       fnd_profile_options_tl tl,
       fnd_user usr
WHERE  opt.profile_option_id = val.profile_option_id
AND    opt.profile_option_name = tl.profile_option_name
AND    usr.user_id = val.last_updated_by
ORDER BY val.last_update_date DESC;

8. Installed Products and Patch Levels

SELECT a.application_name,
       a.product_code,
       DECODE(b.status,
              'I','Installed',
              'S','Shared',
              'N/A') status,
       b.patch_level
FROM   apps.fnd_application_vl a,
       apps.fnd_product_installations b
WHERE  a.application_id = b.application_id
AND    b.status = 'I'
ORDER BY a.product_code;

AD and TXK Code Levels

SELECT abbreviation,
       codelevel
FROM   ad_trackable_entities
WHERE  abbreviation IN ('ad','txk');

9. EBS 12.2 Online Patching / ADOP

Oracle E-Business Suite Release 12.2 uses the ADOP online patching cycle. A normal patching cycle follows the prepare, apply, finalize, cutover and cleanup phases.

source <EBS_ROOT>/EBSapps.env run

adop phase=prepare

adop phase=apply patches=<PATCH_NUMBER>

adop phase=finalize

adop phase=cutover

source <EBS_ROOT>/EBSapps.env run

adop phase=cleanup

Review Recent ADOP Sessions

column id format 99
column nn format a10
column nt format a6

SELECT adop_session_id id,
       prepare_status,
       apply_status,
       finalize_status,
       cutover_status,
       cleanup_status,
       abort_status,
       status,
       node_name nn,
       node_type nt
FROM   ad_adop_sessions
ORDER BY adop_session_id DESC;

Review Patches Associated with ADOP Sessions

SELECT adop_session_id,
       bug_number,
       patchrun_id,
       status,
       node_name,
       CAST(end_date AS TIMESTAMP) end_date,
       driver_file_name,
       patch_top
FROM   ad_adop_session_patches
ORDER BY end_date DESC;

Do not manually update AD_ADOP_SESSIONS or other ADOP metadata tables as a routine recovery method. Investigate the failed ADOP session and use Oracle-supported recovery procedures appropriate to the failure.

10. Database Session Diagnostics

Find Oracle SID from Operating-System Process ID

SELECT a.sid,
       a.serial#,
       b.spid,
       a.username,
       a.osuser,
       a.status,
       a.module
FROM   v$session a,
       v$process b
WHERE  a.paddr = b.addr
AND    b.spid = '&SPID';

11. Tablespace Monitoring

SELECT t.tablespace,
       t.totalspace AS "Total Space (MB)",
       ROUND(t.totalspace-fs.freespace,2) AS "Used Space (MB)",
       fs.freespace AS "Free Space (MB)",
       ROUND(((t.totalspace-fs.freespace)/t.totalspace)*100,2)
          AS "% Used",
       ROUND((fs.freespace/t.totalspace)*100,2)
          AS "% Free"
FROM
(
  SELECT ROUND(SUM(bytes)/(1024*1024)) totalspace,
         tablespace_name tablespace
  FROM   dba_data_files
  GROUP BY tablespace_name
) t,
(
  SELECT ROUND(SUM(bytes)/(1024*1024)) freespace,
         tablespace_name tablespace
  FROM   dba_free_space
  GROUP BY tablespace_name
) fs
WHERE t.tablespace = fs.tablespace
ORDER BY t.tablespace;

Segment Size

SELECT owner,
       segment_name,
       segment_type,
       tablespace_name,
       bytes/1024/1024 MB
FROM   dba_segments
WHERE  owner = UPPER('&OWNER')
AND    segment_name = UPPER('&SEGMENT_NAME');

12. Performance Diagnostics

Monitor Long Operations

SELECT sid,
       serial#,
       opname,
       sofar,
       totalwork,
       ROUND(sofar/totalwork*100,2) "% Complete"
FROM   v$session_longops
WHERE  totalwork > 0
AND    sofar < totalwork;

Some performance scripts based on DBA_HIST_* views access AWR data. Use of AWR and related Diagnostics Pack functionality should be checked against the Oracle licensing applicable to the environment.

13. Users and Responsibilities

List Responsibilities

SELECT
       (SELECT application_short_name
          FROM fnd_application fa
         WHERE fa.application_id = frt.application_id) application,
       frt.responsibility_id,
       frt.responsibility_name
FROM   apps.fnd_responsibility_tl frt
ORDER BY frt.responsibility_name;

Users and Assigned Responsibilities

SELECT DISTINCT
       u.user_id,
       SUBSTR(u.user_name,1,30) user_name,
       SUBSTR(r.responsibility_name,1,60) responsibility,
       SUBSTR(a.application_name,1,50) application
FROM   fnd_user u,
       fnd_user_resp_groups g,
       fnd_application_tl a,
       fnd_responsibility_tl r
WHERE  g.user_id = u.user_id
AND    g.responsibility_application_id = a.application_id
AND    a.application_id = r.application_id
AND    g.responsibility_id = r.responsibility_id
ORDER BY user_name,
         application,
         responsibility;

14. Concurrent Request Trace and Diagnostics

Identify Request and Database Process

SELECT request_id,
       oracle_process_id,
       phase_code,
       status_code,
       actual_start_date,
       actual_completion_date
FROM   apps.fnd_concurrent_requests
WHERE  request_id = '&REQUEST_ID';

The Oracle process ID can then be correlated with V$PROCESS and V$SESSION to identify the database session servicing the request.

15. Production Safety Notes

Avoid indiscriminate process termination.
Commands that terminate every FNDLIBR process using kill -9 can affect multiple concurrent requests and Concurrent Manager processes. Identify the exact process and understand its application impact before taking any termination action.

Avoid direct updates to EBS application tables.
Directly changing phase/status values in FND_CONCURRENT_REQUESTS, ADOP metadata or other EBS-owned tables can leave application metadata inconsistent. Prefer supported application interfaces and documented recovery procedures.

Do not publish or extract database-link passwords.
Credential-recovery SQL and commands containing APPS or other database passwords should not be included in operational documentation or public blog posts.

Recommended practice: Use these scripts primarily for diagnostics and evidence collection. Before executing a command that changes data, terminates a process, modifies patching metadata or changes configuration, confirm the procedure against the documentation appropriate to your exact Oracle E-Business Suite and database release.

Conclusion

A well-organized Apps DBA SQL toolkit can significantly reduce the time required to troubleshoot Oracle E-Business Suite incidents. The most useful approach is to separate read-only diagnostic queries from commands that modify application state, terminate processes or alter configuration.

For day-to-day support, the core areas to monitor are database sessions, Concurrent Processing, Workflow components, application configuration, tablespace utilization, patching status and SQL performance.

Monday, September 21, 2026