Oracle Apps DBA Cookbook
Volume 1 — A day-to-day operational guide
Practical procedures for Oracle E-Business Suite 12.2 on Oracle Database 19c.
Compiled from field practice on large-scale EBS estates. Every recipe is designed to be readable, adaptable, and safe when used with proper change control.
1. Scope & Target Environment
| Dimension | Assumed Baseline | Notes |
|---|---|---|
| EBS release | 12.2.x (12.2.9 / 12.2.10 / 12.2.11+) | Online patching (ADOP), dual filesystem fs1/fs2 |
| Database | 19c (19.3+), Enterprise Edition | Non-CDB and single-PDB both covered |
| Middle tier | WebLogic Server 10.3.6 / FMW 11g | oacore, forms, oafm, forms-c4ws managed servers |
| OS | IBM AIX 7.x (primary), Oracle Linux 7/8 | AIX-specific recipes flagged [AIX] |
| HA | Data Guard physical standby | Broker-managed, Active Data Guard optional |
| Backup | RMAN with catalog or controlfile-only | Disk + tape (TSM/SBT) variants |
2. Recipe Numbering
Every procedure has a stable ID: R<chapter>.<sequence> — for example R06.23.
IDs are permanent. If a recipe is retired it is marked [RETIRED] rather than renumbered, so cross-references, runbooks, and your own notes never break.
3. Recipe Anatomy
### R02.14 — Short imperative title
Use: One line — when you reach for this.
<script / SQL block>
> Note: gotchas, prerequisites, version differences, destructive warnings.4. Risk Flags
| Flag | Meaning |
|---|---|
[READ] | Read-only. Safe on production at any time. |
[WRITE] | Modifies data or configuration. Take a backup first. |
[OUTAGE] | Requires downtime or causes service interruption. |
[AIX] | AIX-specific syntax or command. |
[ROOT] | Needs root or a privileged OS account. |
Unflagged recipes are [READ] by default.
5. Master Index — 572 Recipes
| Ch | Title | Recipes | IDs |
|---|---|---|---|
| 01 | Environment & Foundations | 24 | R01.01–R01.24 |
| 02 | SQL Toolkit — EBS Data Dictionary | 45 | R02.01–R02.45 |
| 03 | Shell Scripts & Automation | 40 | R03.01–R03.40 |
| 04 | Startup, Shutdown & Service Control | 24 | R04.01–R04.24 |
| 05 | AutoConfig | 28 | R05.01–R05.28 |
| 06 | ADOP / Online Patching | 45 | R06.01–R06.45 |
| 07 | Cloning & Refresh | 38 | R07.01–R07.38 |
| 08 | WebLogic, FMW & Middle Tier | 40 | R08.01–R08.40 |
| 09 | Concurrent Processing | 35 | R09.01–R09.35 |
| 10 | RMAN Backup & Recovery | 40 | R10.01–R10.40 |
| 11 | Data Guard | 38 | R11.01–R11.38 |
| 12 | Health Checks & Monitoring | 35 | R12.01–R12.35 |
| 13 | Performance Tuning | 45 | R13.01–R13.45 |
| 14 | Troubleshooting Playbooks | 40 | R14.01–R14.40 |
| 15 | Security, Users & Access | 25 | R15.01–R15.25 |
| 16 | Utilities, Housekeeping & Space | 30 | R16.01–R16.30 |
| Total | 572 |
Build Status
| Chapter | Status |
|---|---|
| 02 — SQL Toolkit | Complete |
| 01, 03–16 | Catalogued — drafting in sequence |
Chapter 02 — SQL Toolkit: The EBS Data Dictionary
45 recipes · R02.01 – R02.45
Run everything here as APPS unless stated otherwise. All recipes are [READ] unless flagged.
Recommended SQL*Plus preamble:
SET LINESIZE 300 PAGESIZE 200 TRIMSPOOL ON
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';Section A — Release, Patches & Editions
R02.01 — Confirm EBS release from the database
Use: First question in every ticket, every Oracle SR.
SELECT release_name,
applications_system_name,
last_update_date
FROM fnd_product_groups;This is the authoritative release. Do not trust the login page banner — it can be stale after a patch until caches clear.
R02.02 — List product installation status and patch levels
Use: Determining which products are shared, installed or inactive before applying a family pack.
SELECT fa.application_short_name AS prod,
fat.application_name,
fpi.patch_level,
DECODE(fpi.status,'I','Installed','S','Shared','N','Not installed',fpi.status) AS status,
fpi.db_status
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fpi.application_id = fa.application_id
AND fa.application_id = fat.application_id
AND fat.language = USERENV('LANG')
ORDER BY fa.application_short_name;R02.03 — Check whether a specific patch has been applied
Use: Oracle Support asks "is patch 12345678 applied?" — answer in ten seconds.
SELECT bug_number,
creation_date,
last_update_date
FROM ad_bugs
WHERE bug_number = '&patch_number';AD_BUGS records the bug/patch number. A row here means the patch was applied to this edition of the database. For 12.2, always confirm which edition you are connected to — see R02.08.R02.04 — List patches applied in a date range
Use: "What changed last weekend?" during a post-change investigation.
SELECT ap.patch_name,
ap.patch_type,
apr.end_date,
apr.success_flag,
apr.appl_top_id
FROM ad_applied_patches ap,
ad_patch_drivers apd,
ad_patch_runs apr
WHERE ap.applied_patch_id = apd.applied_patch_id
AND apd.patch_driver_id = apr.patch_driver_id
AND apr.end_date BETWEEN TO_DATE('&from_date','DD-MON-YYYY')
AND TO_DATE('&to_date','DD-MON-YYYY') + 1
ORDER BY apr.end_date DESC;R02.05 — Show full patch run detail for one patch
Use: Confirming a patch completed on every node, not just the one you ran it from.
SELECT ap.patch_name,
at.name AS appl_top_name,
apr.start_date,
apr.end_date,
apr.success_flag,
apr.patchtop
FROM ad_applied_patches ap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops at
WHERE ap.applied_patch_id = apd.applied_patch_id
AND apd.patch_driver_id = apr.patch_driver_id
AND apr.appl_top_id = at.appl_top_id
AND ap.patch_name = '&patch_number'
ORDER BY apr.start_date;R02.06 — List all ADOP sessions and their phase status
Use: The single most useful ADOP query. Run it before you run any
adop command.SELECT adop_session_id AS session_id,
prepare_status AS prep,
apply_status AS appl,
finalize_status AS fnl,
cutover_status AS cut,
cleanup_status AS clnup,
abort_status AS abrt,
status AS overall,
node_name,
TO_CHAR(prepare_phase_end_date,'DD-MON HH24:MI') AS prep_end,
TO_CHAR(cutover_phase_end_date,'DD-MON HH24:MI') AS cut_end
FROM ad_adop_sessions
ORDER BY adop_session_id DESC
FETCH FIRST 10 ROWS ONLY;Status codes are
Y (completed), N (not done), X (not applicable), F (failed), R (running). A session with status = 'C' is complete. Anything else at the top of this list means you have an open cycle.R02.07 — List patches applied within an ADOP session
Use: Reconstructing exactly what a patch weekend delivered.
SELECT adop_session_id,
bug_number,
patch_file_name,
node_name,
applied_file_system_base,
status,
TO_CHAR(end_date,'DD-MON-YYYY HH24:MI') AS ended
FROM ad_adop_session_patches
WHERE adop_session_id = &session_id
ORDER BY end_date;R02.08 — Show current run and patch editions
Use: Knowing which edition your session is actually in. Get this wrong and every other query misleads you.
SELECT SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') AS my_edition,
ad_zd.get_edition('RUN') AS run_edition,
ad_zd.get_edition('PATCH') AS patch_edition
FROM dual;
SELECT edition_name, parent_edition_name, usable
FROM dba_editions
ORDER BY edition_name;PATCH returns null when no patch cycle is open. If my_edition is not the run edition and you did not intend that, disconnect and re-source your environment.R02.09 — Count objects per edition
Use: Judging how much cleanup debt has accumulated across old editions.
SELECT o.edition_name,
o.object_type,
COUNT(*) AS obj_count
FROM dba_objects_ae o
WHERE o.owner = 'APPS'
AND o.edition_name IS NOT NULL
GROUP BY o.edition_name, o.object_type
ORDER BY o.edition_name, obj_count DESC;Old editions accumulate if
cleanup is skipped. A long tail of editions is a strong signal that full cleanup is overdue.R02.10 — List invalid objects by owner and type
Use: Standard post-patch and post-clone check.
SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;R02.11 — Generate a targeted recompile script for invalids [WRITE]
Use: When
utlrp is too blunt and you want to recompile a specific set.SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SPOOL recompile_invalids.sql
SELECT 'ALTER ' ||
DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' ||
owner || '.' || object_name || ' COMPILE' ||
DECODE(object_type,'PACKAGE BODY',' BODY','') || ';'
FROM dba_objects
WHERE status = 'INVALID'
AND object_type IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER','VIEW','SYNONYM')
ORDER BY DECODE(object_type,'VIEW',1,'SYNONYM',2,'PACKAGE',3,'PACKAGE BODY',4,5);
SPOOL OFF
SET HEADING ON FEEDBACK ON PAGESIZE 200Review the generated file before running it. In 12.2, prefer
adadmin or adop compile options for APPS objects so edition rules are respected.Section B — Topology, Nodes & Profiles
R02.12 — List registered application tier nodes
Use: Verifying
FND_NODES matches physical reality — a frequent source of clone and ADOP failures.SELECT node_name,
node_id,
server_address,
platform_code,
status,
TO_CHAR(creation_date,'DD-MON-YYYY') AS registered
FROM fnd_nodes
ORDER BY node_name;Rows for decommissioned hosts, or a stale
AUTHENTICATION node, will break adop. Clean them with FND_CONC_CLONE.SETUP_CLEAN followed by AutoConfig on every tier — never by direct DELETE.R02.13 — Show which services each node supports
Use: Confirming service distribution matches the intended topology.
SELECT node_name,
support_cp AS conc_proc,
support_forms AS forms,
support_web AS web,
support_admin AS admin,
support_db AS database,
virtual_ip
FROM fnd_nodes
ORDER BY node_name;R02.14 — Retrieve a profile option value at every level
Use: Understanding why a setting behaves differently for one user or responsibility.
SELECT po.profile_option_name AS internal_name,
pot.user_profile_option_name AS display_name,
DECODE(pov.level_id, 10001,'Site',
10002,'Application',
10003,'Responsibility',
10004,'User',
10005,'Server',
10006,'Organization',
10007,'Server+Resp', TO_CHAR(pov.level_id)) AS level_name,
DECODE(pov.level_id, 10002, app.application_short_name,
10003, rsp.responsibility_name,
10004, usr.user_name,
10005, svr.node_name, 'Site') AS level_value,
pov.profile_option_value AS value,
pov.last_update_date
FROM fnd_profile_options po,
fnd_profile_options_tl pot,
fnd_profile_option_values pov,
fnd_application app,
fnd_responsibility_vl rsp,
fnd_user usr,
fnd_nodes svr
WHERE po.profile_option_id = pov.profile_option_id
AND po.application_id = pov.application_id
AND po.profile_option_name = pot.profile_option_name
AND pot.language = USERENV('LANG')
AND pov.level_value = app.application_id (+)
AND pov.level_value = rsp.responsibility_id (+)
AND pov.level_value = usr.user_id (+)
AND pov.level_value = svr.node_id (+)
AND UPPER(pot.user_profile_option_name) LIKE UPPER('%&profile_name%')
ORDER BY pov.level_id;Lower level wins. User beats Responsibility beats Application beats Site.
R02.15 — Find the effective profile value for a specific user
Use: Fastest possible answer when a user reports different behaviour to a colleague.
DECLARE
l_value VARCHAR2(4000);
BEGIN
fnd_global.apps_initialize(
user_id => (SELECT user_id FROM fnd_user WHERE user_name = UPPER('&user_name')),
resp_id => &resp_id,
resp_appl_id => &resp_appl_id);
l_value := fnd_profile.value('&profile_internal_name');
dbms_output.put_line('Effective value: ' || NVL(l_value,'<null>'));
END;
/Requires
SET SERVEROUTPUT ON. Get resp_id and resp_appl_id from R02.18.R02.16 — List recently changed profile options
Use: Post-incident: "did someone change a profile?"
SELECT pot.user_profile_option_name AS profile_name,
pov.level_id,
pov.profile_option_value AS value,
pov.last_update_date,
u.user_name AS changed_by
FROM fnd_profile_option_values pov,
fnd_profile_options po,
fnd_profile_options_tl pot,
fnd_user u
WHERE pov.profile_option_id = po.profile_option_id
AND pov.application_id = po.application_id
AND po.profile_option_name = pot.profile_option_name
AND pot.language = USERENV('LANG')
AND pov.last_updated_by = u.user_id
AND pov.last_update_date > SYSDATE - &days
ORDER BY pov.last_update_date DESC;Section C — Users, Responsibilities & Access
R02.17 — List application users and account status
Use: Access review, dormant account cleanup, licence counting.
SELECT user_name,
description,
TO_CHAR(start_date,'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date,'DD-MON-YYYY') AS end_date,
CASE WHEN end_date IS NULL OR end_date > SYSDATE
THEN 'ACTIVE' ELSE 'INACTIVE' END AS status,
TO_CHAR(last_logon_date,'DD-MON-YYYY') AS last_logon,
employee_id
FROM fnd_user
ORDER BY status, user_name;R02.18 — Show all responsibilities assigned to a user
Use: "I can't see the menu I used yesterday."
SELECT u.user_name,
r.responsibility_name,
r.responsibility_id,
r.application_id AS resp_appl_id,
TO_CHAR(urg.start_date,'DD-MON-YYYY') AS assigned_from,
TO_CHAR(urg.end_date,'DD-MON-YYYY') AS assigned_to,
CASE WHEN urg.end_date IS NULL OR urg.end_date > SYSDATE
THEN 'ACTIVE' ELSE 'ENDED' END AS status
FROM fnd_user u,
fnd_user_resp_groups_direct urg,
fnd_responsibility_vl r
WHERE u.user_id = urg.user_id
AND urg.responsibility_id = r.responsibility_id
AND urg.responsibility_application_id = r.application_id
AND u.user_name = UPPER('&user_name')
ORDER BY status, r.responsibility_name;R02.19 — Show all users holding a given responsibility
Use: Segregation-of-duties review; finding who can approve payments.
SELECT r.responsibility_name,
u.user_name,
u.description,
TO_CHAR(urg.start_date,'DD-MON-YYYY') AS assigned_from,
TO_CHAR(u.last_logon_date,'DD-MON-YYYY') AS last_logon
FROM fnd_user u,
fnd_user_resp_groups_direct urg,
fnd_responsibility_vl r
WHERE u.user_id = urg.user_id
AND urg.responsibility_id = r.responsibility_id
AND urg.responsibility_application_id = r.application_id
AND (urg.end_date IS NULL OR urg.end_date > SYSDATE)
AND UPPER(r.responsibility_name) LIKE UPPER('%&resp_name%')
ORDER BY u.user_name;R02.20 — Map responsibility → request group → concurrent programs
Use: "Why can't this user submit that report?" Ninety percent of the time, the answer is here.
SELECT r.responsibility_name,
rg.request_group_name,
cp.concurrent_program_name AS short_name,
cpt.user_concurrent_program_name AS program_name
FROM fnd_responsibility_vl r,
fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt
WHERE r.request_group_id = rg.request_group_id
AND r.application_id = rg.application_id
AND rg.request_group_id = rgu.request_group_id
AND rg.application_id = rgu.application_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND cpt.language = USERENV('LANG')
AND UPPER(r.responsibility_name) LIKE UPPER('%&resp_name%')
ORDER BY cpt.user_concurrent_program_name;R02.21 — Find users with System Administrator access
Use: The first query any auditor asks for.
SELECT u.user_name,
u.description,
r.responsibility_name,
TO_CHAR(u.last_logon_date,'DD-MON-YYYY') AS last_logon
FROM fnd_user u,
fnd_user_resp_groups_direct urg,
fnd_responsibility_vl r
WHERE u.user_id = urg.user_id
AND urg.responsibility_id = r.responsibility_id
AND urg.responsibility_application_id = r.application_id
AND (urg.end_date IS NULL OR urg.end_date > SYSDATE)
AND (u.end_date IS NULL OR u.end_date > SYSDATE)
AND r.responsibility_name IN ('System Administrator','System Administration',
'Application Developer','Functional Administrator')
ORDER BY r.responsibility_name, u.user_name;R02.22 — Check APPS/APPLSYS database account status and expiry
Use: Preventing the classic "everything died at 2am because the password expired".
SELECT username,
account_status,
TO_CHAR(lock_date,'DD-MON-YYYY') AS locked_on,
TO_CHAR(expiry_date,'DD-MON-YYYY') AS expires_on,
profile,
TO_CHAR(created,'DD-MON-YYYY') AS created
FROM dba_users
WHERE username IN ('APPS','APPLSYS','APPLSYSPUB','SYSTEM','SYS','APPS_NE','EBS_SYSTEM')
OR username LIKE 'XX%'
ORDER BY username;Run as a DBA account.
EBS_SYSTEM exists only on 12.2.10 and later after the EBS System Schema Migration.Section D — Concurrent Processing
R02.23 — List currently running concurrent requests with elapsed time
Use: Every single morning, and the moment anyone says "the system is slow".
SELECT fcr.request_id,
fcpt.user_concurrent_program_name AS program,
fu.user_name AS submitted_by,
TO_CHAR(fcr.actual_start_date,'DD-MON HH24:MI') AS started,
ROUND((SYSDATE - fcr.actual_start_date)*24*60,1) AS mins_running,
fcr.oracle_process_id AS spid,
fcr.os_process_id AS os_pid,
fcr.phase_code, fcr.status_code
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcpt.language = USERENV('LANG')
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'R'
ORDER BY fcr.actual_start_date;phase_code: P=Pending, R=Running, C=Completed, I=Inactive. status_code: N=Normal, E=Error, G=Warning, W=Paused, Q=Standby, R=Normal-running.R02.24 — Map a concurrent request to its database session and OS process
Use: You need to trace, kill, or explain a specific request.
SELECT fcr.request_id,
fcpt.user_concurrent_program_name AS program,
s.sid, s.serial#, s.status,
p.spid AS os_pid,
s.event AS current_wait,
s.sql_id,
ROUND(s.last_call_et/60,1) AS mins_in_call
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
v$session s,
v$process p
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcpt.language = USERENV('LANG')
AND fcr.oracle_process_id = p.spid
AND s.paddr = p.addr
AND fcr.request_id = &request_id;R02.25 — Show concurrent manager status and process counts
Use: Target vs actual processes tells you instantly whether the ICM is healthy.
SELECT fcq.concurrent_queue_name AS queue,
fcqt.user_concurrent_queue_name AS manager,
fcq.max_processes AS target,
fcq.running_processes AS actual,
DECODE(fcq.control_code,'D','Deactivating','E','Deactivated',
'N','Starting up','A','Activating',
'X','Terminated', 'R','Restarting',
NULL,'Running', fcq.control_code) AS control_state,
fcq.enabled_flag,
fcq.target_node
FROM fnd_concurrent_queues fcq,
fnd_concurrent_queues_tl fcqt
WHERE fcq.concurrent_queue_id = fcqt.concurrent_queue_id
AND fcq.application_id = fcqt.application_id
AND fcqt.language = USERENV('LANG')
AND fcq.enabled_flag = 'Y'
ORDER BY fcq.concurrent_queue_name;actual < target on the Standard Manager during working hours is a live problem. actual = 0 on the ICM means the whole subsystem is down.R02.26 — Measure pending request backlog by manager
Use: Deciding whether to add processes or investigate a blockage.
SELECT NVL(fcqt.user_concurrent_queue_name,'<unassigned>') AS manager,
COUNT(*) AS pending_count,
MIN(fcr.requested_start_date) AS oldest_queued,
ROUND((SYSDATE - MIN(fcr.requested_start_date))*24*60,1) AS oldest_wait_mins
FROM fnd_concurrent_requests fcr,
fnd_concurrent_queues fcq,
fnd_concurrent_queues_tl fcqt
WHERE fcr.concurrent_queue_id = fcq.concurrent_queue_id (+)
AND fcr.queue_application_id = fcq.application_id (+)
AND fcq.concurrent_queue_id = fcqt.concurrent_queue_id (+)
AND fcq.application_id = fcqt.application_id (+)
AND fcqt.language (+) = USERENV('LANG')
AND fcr.phase_code = 'P'
AND fcr.requested_start_date <= SYSDATE
GROUP BY fcqt.user_concurrent_queue_name
ORDER BY pending_count DESC;R02.27 — List requests completed in error in the last 24 hours
Use: Morning check. Ideally this returns nothing.
SELECT fcr.request_id,
fcpt.user_concurrent_program_name AS program,
fu.user_name AS submitted_by,
TO_CHAR(fcr.actual_completion_date,'DD-MON HH24:MI') AS completed,
fcr.status_code,
SUBSTR(fcr.completion_text,1,120) AS completion_text
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcpt.language = USERENV('LANG')
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'C'
AND fcr.status_code IN ('E','G','T')
AND fcr.actual_completion_date > SYSDATE - 1
ORDER BY fcr.actual_completion_date DESC;R02.28 — Find the longest-running programs over the last 7 days
Use: Building the tuning candidate list, and spotting runtime regression.
SELECT fcpt.user_concurrent_program_name AS program,
COUNT(*) AS runs,
ROUND(AVG((fcr.actual_completion_date - fcr.actual_start_date)*24*60),1) AS avg_mins,
ROUND(MAX((fcr.actual_completion_date - fcr.actual_start_date)*24*60),1) AS max_mins,
ROUND(SUM((fcr.actual_completion_date - fcr.actual_start_date)*24*60),1) AS total_mins
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt
WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcpt.language = USERENV('LANG')
AND fcr.phase_code = 'C'
AND fcr.actual_start_date > SYSDATE - 7
AND fcr.actual_completion_date IS NOT NULL
GROUP BY fcpt.user_concurrent_program_name
HAVING SUM((fcr.actual_completion_date - fcr.actual_start_date)*24*60) > 30
ORDER BY total_mins DESC
FETCH FIRST 25 ROWS ONLY;Order by
total_mins, not max_mins. A 90-second program running 4,000 times a day costs you more than one nightly 40-minute batch.R02.29 — Look up a concurrent program definition by name
Use: Finding the short name, application and execution method before you trace or clone it.
SELECT cpt.user_concurrent_program_name AS program_name,
cp.concurrent_program_name AS short_name,
fa.application_short_name AS application,
cp.enabled_flag,
cp.execution_method_code,
cp.enable_trace,
cp.run_alone_flag
FROM fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa
WHERE cp.concurrent_program_id = cpt.concurrent_program_id
AND cp.application_id = cpt.application_id
AND cp.application_id = fa.application_id
AND cpt.language = USERENV('LANG')
AND UPPER(cpt.user_concurrent_program_name) LIKE UPPER('%&program_name%')
ORDER BY cpt.user_concurrent_program_name;R02.30 — Find the executable and file behind a concurrent program
Use: Locating the actual
.sql, .prog, .rdf or Java class you need to inspect or patch.SELECT cpt.user_concurrent_program_name AS program_name,
cp.concurrent_program_name AS short_name,
fe.executable_name,
fe.execution_file_name,
DECODE(fe.execution_method_code,
'I','PL/SQL Stored Procedure','P','Oracle Reports',
'H','Host','S','Immediate','J','Java Concurrent Program',
'K','Java Stored Procedure','L','SQL*Loader','Q','SQL*Plus',
'B','Request Set Stage Function','A','Spawned',
fe.execution_method_code) AS exec_method,
fa.application_short_name AS exec_application
FROM fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_executables fe,
fnd_application fa
WHERE cp.concurrent_program_id = cpt.concurrent_program_id
AND cp.application_id = cpt.application_id
AND cp.executable_id = fe.executable_id
AND cp.executable_application_id = fe.application_id
AND fe.application_id = fa.application_id
AND cpt.language = USERENV('LANG')
AND UPPER(cpt.user_concurrent_program_name) LIKE UPPER('%&program_name%');R02.31 — Report request volume by user and responsibility
Use: Capacity planning, and identifying who is generating the load.
SELECT fu.user_name,
fr.responsibility_name,
COUNT(*) AS request_count,
ROUND(SUM((fcr.actual_completion_date - fcr.actual_start_date)*24*60),1) AS total_mins
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_responsibility_vl fr
WHERE fcr.requested_by = fu.user_id
AND fcr.responsibility_id = fr.responsibility_id (+)
AND fcr.responsibility_application_id = fr.application_id (+)
AND fcr.request_date > SYSDATE - &days
GROUP BY fu.user_name, fr.responsibility_name
ORDER BY request_count DESC
FETCH FIRST 30 ROWS ONLY;Section E — Sessions, Locks & Contention
R02.32 — Identify blocking sessions and the blocked chain
Use: "The screen is frozen." This is almost always the answer.
SELECT LPAD(' ', 2*(LEVEL-1)) || s.sid AS blocking_tree,
s.sid, s.serial#, s.username,
s.module, s.action,
s.status,
s.event AS waiting_on,
ROUND(s.seconds_in_wait/60,1) AS wait_mins,
s.blocking_session,
p.spid AS os_pid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
START WITH s.blocking_session IS NULL
AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY s.sid;The root of each tree is the true culprit. Killing a leaf achieves nothing.
R02.33 — Show active EBS sessions with module, action and client info
Use: Attributing a database session back to a real person or a request.
SELECT s.sid, s.serial#, s.username AS db_user,
fu.user_name AS ebs_user,
s.module, s.action,
s.machine, s.program,
s.status,
s.sql_id,
s.event,
ROUND(s.last_call_et/60,1) AS mins_in_call,
p.spid AS os_pid
FROM v$session s,
v$process p,
fnd_logins fl,
fnd_user fu
WHERE s.paddr = p.addr
AND s.audsid = fl.spid (+)
AND fl.user_id = fu.user_id (+)
AND s.username = 'APPS'
AND s.status = 'ACTIVE'
AND s.type = 'USER'
ORDER BY s.last_call_et DESC;FND_LOGINS linkage is only reliable when Sign-On Audit is enabled. MODULE set by EBS usually carries the form or program name and is the more dependable clue.R02.34 — Generate a kill-session script for a request or session [WRITE]
Use: Terminating a runaway request cleanly, with an audit trail of exactly what you killed.
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial#
|| ',@' || s.inst_id || ''' IMMEDIATE; -- req ' || fcr.request_id
|| ' user ' || fu.user_name AS kill_command
FROM gv$session s,
gv$process p,
fnd_concurrent_requests fcr,
fnd_user fu
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND fcr.oracle_process_id = p.spid
AND fcr.requested_by = fu.user_id
AND fcr.request_id = &request_id;Always cancel the request from the Concurrent Requests form first and give it a minute. Killing the session leaves the request in Running with no process, which then needs the manager cleanup procedure. Never kill the ICM's own session.
Section F — Space, Storage & Statistics
R02.35 — Summarise tablespace usage and headroom
Use: Daily. This and the alert log are the two checks you never skip.
SELECT df.tablespace_name,
ROUND(df.alloc_mb) AS alloc_mb,
ROUND(df.max_mb) AS max_mb,
ROUND(df.alloc_mb - NVL(fs.free_mb,0)) AS used_mb,
ROUND(100*(df.alloc_mb - NVL(fs.free_mb,0))/df.alloc_mb,1) AS pct_used_alloc,
ROUND(100*(df.alloc_mb - NVL(fs.free_mb,0))/df.max_mb,1) AS pct_used_max,
df.file_count
FROM (SELECT tablespace_name,
SUM(bytes)/1024/1024 AS alloc_mb,
SUM(GREATEST(bytes, NVL(maxbytes,bytes)))/1024/1024 AS max_mb,
COUNT(*) AS file_count
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name (+)
ORDER BY pct_used_max DESC;pct_used_max is the number that matters. A tablespace at 98% allocated but 40% of maximum is fine; one at 60% allocated with autoextend off is not.R02.36 — List the top segments by size
Use: Finding what is actually consuming your growth before you add another datafile.
SELECT owner, segment_name, segment_type, tablespace_name,
ROUND(bytes/1024/1024/1024,2) AS size_gb,
partition_name
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 30 ROWS ONLY;R02.37 — Report datafile autoextend configuration and ceiling
Use: Catching the datafile with autoextend off before it catches you at 3am.
SELECT tablespace_name,
file_name,
ROUND(bytes/1024/1024) AS current_mb,
autoextensible,
ROUND(increment_by * (SELECT value/1024/1024 FROM v$parameter WHERE name='db_block_size'),1) AS next_mb,
ROUND(maxbytes/1024/1024) AS max_mb
FROM dba_data_files
WHERE autoextensible = 'NO'
OR maxbytes < bytes * 1.2
ORDER BY tablespace_name, file_name;R02.38 — Show temp tablespace usage by session
Use: Identifying the one query eating 200GB of temp.
SELECT s.sid, s.serial#, s.username, s.module,
s.sql_id,
ROUND(SUM(u.blocks) * (SELECT value FROM v$parameter WHERE name='db_block_size')
/1024/1024/1024, 2) AS temp_gb,
u.tablespace
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
GROUP BY s.sid, s.serial#, s.username, s.module, s.sql_id, u.tablespace
ORDER BY temp_gb DESC;R02.39 — Report undo usage, retention and tuned retention
Use: Diagnosing ORA-01555 and sizing undo for long-running batch.
SELECT (SELECT value FROM v$parameter WHERE name='undo_retention') AS undo_retention_s,
(SELECT TO_CHAR(tuned_undoretention) FROM v$undostat
WHERE ROWNUM = 1 ORDER BY begin_time DESC) AS tuned_retention_s,
(SELECT ROUND(SUM(bytes)/1024/1024/1024,2) FROM dba_data_files
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name='undo_tablespace')) AS undo_gb,
(SELECT COUNT(*) FROM dba_undo_extents WHERE status='ACTIVE') AS active_extents,
(SELECT COUNT(*) FROM dba_undo_extents WHERE status='EXPIRED') AS expired_extents
FROM dual;R02.40 — Check FND_STATS gather history by schema
Use: Confirming the "Gather Schema Statistics" request actually did what it claimed.
SELECT schema_name,
COUNT(*) AS objects_gathered,
MIN(last_gather_start_time) AS first_start,
MAX(last_gather_end_time) AS last_end
FROM fnd_stats_hist
WHERE last_gather_start_time > SYSDATE - &days
GROUP BY schema_name
ORDER BY last_end DESC;In EBS, always gather statistics through
FND_STATS or the Gather Schema Statistics concurrent program — not raw DBMS_STATS. FND_STATS applies the EBS-specific settings and histogram handling.R02.41 — Identify objects with stale or missing optimizer statistics
Use: Explaining a sudden plan change after a large data load.
SELECT owner, table_name, num_rows,
TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI') AS last_analyzed,
stale_stats,
partitioned
FROM dba_tab_statistics
WHERE owner IN ('APPS','APPLSYS','AP','AR','GL','INV','ONT','PO','WIP','XX_CUSTOM')
AND (stale_stats = 'YES' OR last_analyzed IS NULL)
AND object_type = 'TABLE'
AND NVL(num_rows,0) > 10000
ORDER BY num_rows DESC NULLS FIRST
FETCH FIRST 40 ROWS ONLY;R02.42 — Report FND_LOBS size and SecureFile conversion status
Use:
FND_LOBS is routinely the largest object in an EBS database. Check it monthly.SELECT l.owner, l.table_name, l.column_name,
l.segment_name,
l.securefile,
l.compression, l.deduplication,
ROUND(s.bytes/1024/1024/1024,2) AS lob_gb
FROM dba_lobs l, dba_segments s
WHERE l.segment_name = s.segment_name
AND l.owner = s.owner
AND l.table_name = 'FND_LOBS'
ORDER BY s.bytes DESC;
-- Row count and age profile
SELECT TO_CHAR(upload_date,'YYYY-MM') AS month,
COUNT(*) AS rows_loaded,
ROUND(SUM(DBMS_LOB.GETLENGTH(file_data))/1024/1024/1024,2) AS gb
FROM applsys.fnd_lobs
GROUP BY TO_CHAR(upload_date,'YYYY-MM')
ORDER BY month DESC
FETCH FIRST 24 ROWS ONLY;The second query reads every LOB and is expensive on a large table — run it off-hours. Conversion from BasicFile to SecureFile is covered in R16.07.
R02.43 — Inventory custom (XX*) schemas and objects
Use: Impact assessment before an upgrade, and handover documentation.
SELECT owner, object_type, COUNT(*) AS obj_count,
MAX(last_ddl_time) AS most_recent_change
FROM dba_objects
WHERE owner LIKE 'XX%'
OR (owner = 'APPS' AND object_name LIKE 'XX%')
GROUP BY owner, object_type
ORDER BY owner, obj_count DESC;R02.44 — Check Workflow backlog: open items and notifications
Use: Approvals not arriving is nearly always visible here first.
-- Open workflow items by type
SELECT item_type,
COUNT(*) AS open_items,
MIN(begin_date) AS oldest,
ROUND(SYSDATE - MIN(begin_date)) AS oldest_days
FROM wf_items
WHERE end_date IS NULL
GROUP BY item_type
ORDER BY open_items DESC;
-- Notification mailer backlog
SELECT status, mail_status, COUNT(*) AS notif_count,
MIN(begin_date) AS oldest
FROM wf_notifications
WHERE status = 'OPEN'
GROUP BY status, mail_status
ORDER BY notif_count DESC;A growing count with
mail_status = 'MAIL' means the notification mailer is not sending. See Ch. 09 R09.32.R02.45 — Verify database initialization parameters against EBS requirements
Use: After any DB patch, clone or parameter change. Deviations here cause the strangest bugs.
SELECT name,
value,
isdefault,
ismodified,
description
FROM v$parameter
WHERE name IN ('compatible','optimizer_features_enable','nls_length_semantics',
'nls_comp','nls_sort','_system_trig_enabled','sga_target',
'pga_aggregate_target','processes','sessions','session_cached_cursors',
'open_cursors','db_block_size','db_files','undo_management',
'plsql_code_type','plsql_optimize_level','optimizer_adaptive_plans',
'parallel_max_servers','job_queue_processes','max_string_size',
'shared_pool_size','result_cache_max_size','db_writer_processes')
ORDER BY name;
-- Non-default hidden parameters (frequent source of surprise after a clone)
SELECT a.ksppinm AS parameter, b.ksppstvl AS value, b.ksppstdf AS is_default
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm LIKE '\_%' ESCAPE '\'
AND b.ksppstdf = 'FALSE'
ORDER BY a.ksppinm;Run the second query as SYS. Compare the result against the EBS 12.2 database parameter note for your exact release before changing anything — some underscore parameters are mandatory for EBS and must not be removed.
