Thursday, August 13, 2026

ter

sed '/^[[:space:]]*P[[:space:]]*$/d' ~/.profile > /tmp/profile.$$

cp /tmp/profile.$$ ~/.profile

rm /tmp/profile.$$

nl -ba ~/.profile | head -20

Thursday, August 6, 2026

Troubleshooting an Idle or Stuck Oracle E-Business Suite Forms Session

Oracle Enterprise Manager may show an Oracle E-Business Suite Forms session as idle, long-running, or potentially stuck. Before terminating it, identify the exact database session, map it to the EBS application user, examine its recent activity, and confirm that it is safe to disconnect.

All identifiers in the examples below are represented by placeholders to protect user and environment information:

<SERIAL_NUMBER>
<SID>
<INSTANCE_ID>
<FORM_MODULE>
<CLIENT_IDENTIFIER>
<SQL_ID>
<INDEX_OWNER>
<INDEX_NAME>

A serial number alone does not uniquely identify a database session. Always confirm the SID, serial number, instance, username, module, client identifier, and logon time before taking action.

1. Identify the Exact Database Session

Use the serial number and Forms module captured from the monitoring tool:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.logon_time,
       s.last_call_et,
       s.blocking_instance,
       s.blocking_session,
       s.event,
       s.wait_class,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       s.client_identifier,
       s.process,
       s.paddr
FROM   gv$session s
WHERE  s.serial# = <SERIAL_NUMBER>
AND    s.module LIKE '%<FORM_MODULE>%';

GV$SESSION works for both Oracle RAC and single-instance databases. For a non-RAC database, V$SESSION can also be used.

Validate:

SID and serial number
Instance ID
Database username
Module and action
Client identifier
Logon time
Current and previous SQL IDs
Current wait event
Blocking-session details

LAST_CALL_ET is measured in seconds:

For an ACTIVE session, it shows how long the current call has been active.
For an INACTIVE session, it shows how long the session has been inactive.

An inactive session is not automatically a problem.

2. Identify the EBS Application User

Oracle EBS commonly stores the ICX session ID in CLIENT_IDENTIFIER. It can be used to identify the application user and responsibility:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.client_identifier,
       ic.session_id,
       fu.user_name,
       fr.responsibility_name,
       ic.disable_date
FROM   gv$session s
JOIN   apps.icx_sessions ic
       ON s.client_identifier = TO_CHAR(ic.session_id)
JOIN   apps.fnd_user fu
       ON ic.user_id = fu.user_id
LEFT JOIN apps.fnd_responsibility_tl fr
       ON ic.responsibility_id = fr.responsibility_id
      AND fr.language = 'US'
WHERE  s.serial# = <SERIAL_NUMBER>
AND    s.module LIKE '%<FORM_MODULE>%';

This establishes:

The named EBS user
The responsibility used
The associated ICX session
Whether the application session has been disabled

Change the language condition if the environment uses a language other than US English.

This mapping is valid only when CLIENT_IDENTIFIER contains the EBS ICX session ID. Confirm the implementation in your environment.

3. Check Whether the Session Is Blocking Others

Display all currently blocked sessions:

SELECT w.inst_id,
       w.sid,
       w.serial#,
       w.username,
       w.module,
       w.blocking_instance,
       w.blocking_session,
       w.wait_class,
       w.event,
       w.seconds_in_wait
FROM   gv$session w
WHERE  w.blocking_session IS NOT NULL
ORDER BY w.blocking_instance,
         w.blocking_session,
         w.inst_id,
         w.sid;

Check specifically whether the identified session is blocking another session:

SELECT w.inst_id,
       w.sid,
       w.serial#,
       w.username,
       w.module,
       w.event,
       w.wait_class,
       w.seconds_in_wait
FROM   gv$session w
WHERE  w.blocking_session  = <SID>
AND    w.blocking_instance = <INSTANCE_ID>;

No rows means Oracle does not currently report another session as directly blocked by this session. Historical or intermittent blocking may still require ASH or application-log analysis.

4. Review the Current and Previous SQL

The session query returns SQL_ID and PREV_SQL_ID. For an inactive session, PREV_SQL_ID is often more useful because no statement may currently be executing.

SELECT inst_id,
       sql_id,
       plan_hash_value,
       sql_text,
       executions,
       elapsed_time / 1e6 AS elapsed_sec,
       cpu_time / 1e6     AS cpu_sec,
       disk_reads,
       buffer_gets,
       rows_processed,
       last_active_time
FROM   gv$sqlarea
WHERE  sql_id = '<SQL_ID>'
ORDER BY inst_id,
         last_active_time DESC;

Review:

SQL text
Execution count
Elapsed and CPU time
Disk reads and buffer gets
Rows processed
Plan hash value
Last active time

The values in GV$SQLAREA are cumulative for the shared cursor. They may include executions by multiple sessions and should not be attributed entirely to one user.

Retrieve Aged-Out SQL from AWR

If the SQL is no longer in the shared pool and the organization is licensed for Oracle Diagnostics Pack:

SELECT st.dbid,
       st.sql_id,
       st.sql_text
FROM   dba_hist_sqltext st
WHERE  st.sql_id = '<SQL_ID>';

Retrieve its historical performance:

SELECT sn.begin_interval_time,
       ss.instance_number,
       ss.snap_id,
       ss.plan_hash_value,
       ss.executions_delta,
       ss.elapsed_time_delta / 1e6 AS elapsed_sec,
       ss.cpu_time_delta / 1e6     AS cpu_sec,
       ss.buffer_gets_delta,
       ss.disk_reads_delta,
       ss.rows_processed_delta
FROM   dba_hist_sqlstat ss
JOIN   dba_hist_snapshot sn
       ON sn.dbid = ss.dbid
      AND sn.instance_number = ss.instance_number
      AND sn.snap_id = ss.snap_id
WHERE  ss.sql_id = '<SQL_ID>'
ORDER BY sn.begin_interval_time DESC,
         ss.instance_number;

AWR and DBA_HIST_* views require the appropriate Oracle Diagnostics Pack licence.

5. Review Recent Wait History

Use the SID and instance ID identified earlier:

SELECT inst_id,
       sid,
       seq#,
       event,
       wait_time,
       p1,
       p2,
       p3
FROM   gv$session_wait_history
WHERE  sid     = <SID>
AND    inst_id = <INSTANCE_ID>
ORDER BY seq#;

This view contains only a small, recent in-memory history. It may show what the session was waiting for immediately before entering its current state.

Common idle events include:

SQL*Net message from client
SQL*Net message to client

An idle network wait usually means the database is waiting for the application or user to submit another request. It does not, by itself, prove that the session is stuck.

6. Determine Whether the Issue Is Isolated or Systemic

Review all sessions using the same Oracle Forms module:

SELECT inst_id,
       sid,
       serial#,
       username,
       client_identifier,
       status,
       last_call_et,
       sql_id,
       prev_sql_id,
       event,
       wait_class,
       blocking_instance,
       blocking_session,
       logon_time
FROM   gv$session
WHERE  module LIKE '%<FORM_MODULE>%'
ORDER BY last_call_et DESC;

This helps determine:

Whether only one user is affected
Whether multiple users on the same form are inactive
Whether multiple sessions have the same wait event
Whether the sessions executed the same SQL
Whether they share a common blocker
Whether the problem began around the same time

If multiple sessions have the same non-idle wait or SQL ID, the issue may be systemic.

7. Check the Relevant Database Object

If SQL analysis identifies an index involved in the activity, review its condition and statistics:

SELECT owner,
       index_name,
       table_owner,
       table_name,
       status,
       visibility,
       num_rows,
       distinct_keys,
       last_analyzed,
       blevel,
       leaf_blocks,
       clustering_factor
FROM   dba_indexes
WHERE  owner      = '<INDEX_OWNER>'
AND    index_name = '<INDEX_NAME>';

Do not rebuild or modify an index simply because:

Its statistics are old
Its clustering factor is high
It appeared in an execution plan
It was the last object observed in a monitoring tool

First establish evidence of an actual access-path, corruption, usability, or performance problem.

For Oracle EBS objects, use Oracle-supported maintenance procedures and the appropriate EBS statistics-gathering programs.

8. Build an ASH Activity Timeline

If Oracle Diagnostics Pack is licensed, query Active Session History:

SELECT sample_time,
       inst_id,
       session_id,
       session_serial#,
       sql_id,
       sql_plan_hash_value,
       event,
       wait_class,
       blocking_inst_id,
       blocking_session,
       session_state,
       module,
       action
FROM   gv$active_session_history
WHERE  session_id      = <SID>
AND    session_serial# = <SERIAL_NUMBER>
AND    inst_id         = <INSTANCE_ID>
ORDER BY sample_time DESC;

ASH samples active sessions. A session that has remained idle may therefore have few or no recent samples.

For activity outside the in-memory ASH retention period, licensed environments can query DBA_HIST_ACTIVE_SESS_HISTORY.

ASH, historical ASH, and AWR require the appropriate Oracle Diagnostics Pack licence.

9. Check for an Open Transaction

An Oracle session can be inactive while still holding an uncommitted transaction:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       t.start_time,
       t.used_ublk,
       t.used_urec
FROM   gv$session s
JOIN   gv$transaction t
       ON t.inst_id = s.inst_id
      AND t.addr    = s.taddr
WHERE  s.sid     = <SID>
AND    s.serial# = <SERIAL_NUMBER>
AND    s.inst_id = <INSTANCE_ID>;

If the query returns a row, the session has an active transaction. Terminating it will cause Oracle to roll back that transaction. The rollback may take considerable time depending on the amount of undo generated.

10. Terminate the Session Only After Validation

Terminate a session only after confirming:

The SID, serial number, and instance are correct.
The session still belongs to the expected EBS user and module.
It is stale, orphaned, or causing a confirmed problem.
The user has confirmed there is no unsaved work.
Application and business approvals have been obtained.
The impact of rolling back any open transaction has been assessed.
Diagnostic evidence has been saved.

For a single-instance database:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL_NUMBER>' IMMEDIATE;

For Oracle RAC:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL_NUMBER>,@<INSTANCE_ID>' IMMEDIATE;

Immediately before executing the command, rerun the session-identification query. This prevents terminating the wrong session if the original session disconnected and its SID was reused.

Confirm the result:

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       server,
       event,
       module,
       client_identifier
FROM   gv$session
WHERE  sid     = <SID>
AND    serial# = <SERIAL_NUMBER>
AND    inst_id = <INSTANCE_ID>;

The session may temporarily remain visible with a KILLED status while Oracle completes cleanup or transaction rollback.

Recommended Troubleshooting Sequence

Identify the exact database session.
Map it to the EBS application user.
Check for blocked sessions.
Check for an open transaction.
Review the current and previous SQL.
Examine recent waits and historical activity.
Compare other sessions using the same form.
Assess the application and business impact.
Capture evidence and obtain approval.
Revalidate the session identity.
Terminate it only when necessary and safe.

A long-running or inactive Oracle EBS Forms session should not be killed solely because it has remained connected for an extended period. An evidence-based investigation reduces the risk of disconnecting the wrong user, losing unsaved application work, or initiating a large transaction rollback.

Wednesday, August 5, 2026

Oracle Apps DBA Cookbook

Oracle Apps DBA Cookbook — Volume 1 | 572 Practical Recipes for EBS 12.2

Oracle Apps DBA Cookbook

Volume 1 — A day-to-day operational guide

572 Recipes EBS 12.2 Oracle Database 19c

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 200

Review 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.

Friday, July 31, 2026

ref

Wednesday, July 29, 2026

workdlow

-- Which Workflow processes are generating the most events?

SELECT

event_name,

event_key,

COUNT(*) AS event_count,

MAX(event_date) AS latest_event

FROM wf_events

WHERE event_date >= TRUNC(SYSDATE) + (18/24)

GROUP BY event_name, event_key

ORDER BY event_count DESC

FETCH FIRST 20 ROWS ONLY;

-- Check WF_EVENT listener queue backlog

SELECT

qt.queue_table,

q.name,

q.status,

DBMS_AQADM.queue_depth(q.name) AS queue_depth

FROM dba_queues q

JOIN dba_queue_tables qt ON q.queue_table = qt.queue_table

WHERE q.name IN ('WF_EVENT_T', 'WF_EVENT_Q');

-- Find expensive WF_EVENT queries

SELECT

sql_id,

parsing_schema_name,

executions,

elapsed_time / 1e6 AS elapsed_sec,

ROUND(elapsed_time / executions / 1e3, 2) AS ms_per_exec,

sql_text

FROM v$sqlarea

WHERE LOWER(sql_text) LIKE '%wf_event%'

AND executions > 0

ORDER BY elapsed_time DESC

FETCH FIRST 10 ROWS ONLY;

-- Check WF_EVENT listener latency

SELECT

event_filter_guid,

event_name,

COUNT(*) AS processed_count,

MAX(event_date) AS last_processed

FROM wf_event_subscriptions

WHERE event_date >= TRUNC(SYSDATE) + (18/24)

GROUP BY event_filter_guid, event_name

ORDER BY processed_count DESC;

-- Which Workflow processes are generating the most events?

SELECT

event_name,

event_key,

COUNT(*) AS event_count,

MAX(event_date) AS latest_event

FROM wf_events

WHERE event_date >= TRUNC(SYSDATE) + (18/24)

GROUP BY event_name, event_key

ORDER BY event_count DESC

FETCH FIRST 20 ROWS ONLY;

-- Check WF_EVENT listener queue backlog

SELECT

qt.queue_table,

q.name,

q.status,

DBMS_AQADM.queue_depth(q.name) AS queue_depth

FROM dba_queues q

JOIN dba_queue_tables qt ON q.queue_table = qt.queue_table

WHERE q.name IN ('WF_EVENT_T', 'WF_EVENT_Q');

-- Find expensive WF_EVENT queries

SELECT

sql_id,

parsing_schema_name,

executions,

elapsed_time / 1e6 AS elapsed_sec,

ROUND(elapsed_time / executions / 1e3, 2) AS ms_per_exec,

sql_text

FROM v$sqlarea

WHERE LOWER(sql_text) LIKE '%wf_event%'

AND executions > 0

ORDER BY elapsed_time DESC

FETCH FIRST 10 ROWS ONLY;

-- Check WF_EVENT listener latency

SELECT

event_filter_guid,

event_name,

COUNT(*) AS processed_count,

MAX(event_date) AS last_processed

FROM wf_event_subscriptions

WHERE event_date >= TRUNC(SYSDATE) + (18/24)

GROUP BY event_filter_guid, event_name

ORDER BY processed_count DESC;

-- Trace where setNavigationParama is being called FROM

SELECT

sql_id,

parsing_schema_name,

executions,

ROUND(elapsed_time / 1e6 / executions, 2) AS avg_sec_per_exec,

SUBSTR(sql_text, 1, 100) AS sql_snippet

FROM v$sqlarea

WHERE sql_id = '0bu1jgc94rg3fj'

OR LOWER(sql_text) LIKE '%setnavigationparam%';

-- Find what's IN THE CALL STACK for this SQL

SELECT

sql_id,

plan_hash_value,

depth,

operation,

options,

object_name,

object_type,

cardinality,

bytes

FROM v$sql_plan

WHERE sql_id = '0bu1jgc94rg3fj'

ORDER BY id;

-- Get the actual SQL text

SELECT

sql_fulltext

FROM v$sql

WHERE sql_id = '0bu1jgc94rg3fj'

FETCH FIRST 1 ROW ONLY;

-- Find what's calling setNavigationParams

SELECT

sql_id,

parsing_schema_name,

executions,

SUBSTR(sql_text, 1, 200) AS sql_caller

FROM v$sqlarea

WHERE LOWER(sql_text) LIKE '%setnavigationparam%'

OR (sql_id != '0bu1jgc94rg3fj'

AND LOWER(sql_text) LIKE '%wf_event%'

AND executions > 1000)

ORDER BY executions DESC

FETCH FIRST 20 ROWS ONLY;

-- Check concurrent request log for WF jobs

SELECT

request_id,

program_application_id,

concurrent_program_id,

phase_code,

status_code,

actual_start_date,

actual_completion_date,

argument_text

FROM fnd_concurrent_requests

WHERE program_application_id IN (

SELECT application_id FROM fnd_application WHERE application_short_name IN ('WF', 'FUN')

)

AND actual_start_date >= TRUNC(SYSDATE - 7)

AND status_code IN ('R', 'C', 'E') -- Running, Complete, Error

ORDER BY request_id DESC

FETCH FIRST 30 ROWS ONLY;

-- Check for stuck/errored requests

SELECT

request_id,

user_concurrent_program_name,

phase_code,

status_code,

actual_start_date,

ROUND((SYSDATE - actual_start_date) * 86400, 0) AS age_seconds,

logfile_node_name,

logfile_path,

logfile_filename

FROM fnd_concurrent_requests_vl

WHERE status_code IN ('R', 'E')

AND actual_start_date >= TRUNC(SYSDATE - 1)

ORDER BY actual_start_date DESC;

-- Find what's calling setNavigationParams

SELECT

sql_id,

parsing_schema_name,

executions,

SUBSTR(sql_text, 1, 200) AS sql_caller

FROM v$sqlarea

WHERE LOWER(sql_text) LIKE '%setnavigationparam%'

OR (sql_id != '0bu1jgc94rg3fj'

AND LOWER(sql_text) LIKE '%wf_event%'

AND executions > 1000)

ORDER BY executions DESC

FETCH FIRST 20 ROWS ONLY;

-- Check concurrent request log for WF jobs

SELECT

request_id,

program_application_id,

concurrent_program_id,

phase_code,

status_code,

actual_start_date,

actual_completion_date,

argument_text

FROM fnd_concurrent_requests

WHERE program_application_id IN (

SELECT application_id FROM fnd_application WHERE application_short_name IN ('WF', 'FUN')

)

AND actual_start_date >= TRUNC(SYSDATE - 7)

AND status_code IN ('R', 'C', 'E') -- Running, Complete, Error

ORDER BY request_id DESC

FETCH FIRST 30 ROWS ONLY;

-- Check for stuck/errored requests

SELECT

request_id,

user_concurrent_program_name,

phase_code,

status_code,

actual_start_date,

ROUND((SYSDATE - actual_start_date) * 86400, 0) AS age_seconds,

logfile_node_name,

logfile_path,

logfile_filename

FROM fnd_concurrent_requests_vl

WHERE status_code IN ('R', 'E')

AND actual_start_date >= TRUNC(SYSDATE - 1)

ORDER BY actual_start_date DESC;

SELECT

request_id,

user_concurrent_program_name,

concurrent_program_name,

program_application_id,

concurrent_program_id,

actual_start_date,

actual_completion_date,

ROUND((actual_completion_date - actual_start_date) * 86400, 0) AS duration_sec,

phase_code,

status_code

FROM fnd_concurrent_requests_vl

WHERE actual_start_date >= TRUNC(SYSDATE - 2)

AND (status_code IN ('R', 'E', 'I')

OR actual_start_date BETWEEN TO_DATE('2026-07-25', 'YYYY-MM-DD') AND TO_DATE('2026-07-28', 'YYYY-MM-DD'))

ORDER BY actual_start_date DESC

FETCH FIRST 50 ROWS ONLY;

SELECT

owner,

name,

type

FROM dba_source

WHERE owner = 'APPS'

AND (LOWER(text) LIKE '%WF_ITEM_ACTIVITY_STATUSES%'

OR LOWER(text) LIKE '%OJMSTEXT%')

GROUP BY owner, name, type

ORDER BY name;

-- Find sessions currently executing the loop

SELECT

sid,

serial#,

username,

program,

status,

sql_id,

ROUND((SYSDATE - logon_time) * 86400, 0) AS session_age_sec,

event

FROM v$session

WHERE username = 'APPS'

AND status = 'ACTIVE'

AND (sql_id IN ('5z0s7q1a976mp', '8qf0gsw8scbr', 'a4gzfqk1c25n',

'daphqlqsnymv8', '0bu1jgc94rg3fj')

OR ROUND((SYSDATE - logon_time) * 86400) > 3600);

SELECT

s.sid,

s.serial#,

s.sql_id,

s.prev_sql_id,

s.status,

s.username,

s.module,

s.action,

s.client_identifier,

s.client_info,

s.program,

s.machine,

s.process AS client_os_pid,

p.spid AS database_os_pid,

s.logon_time

FROM gv$session s

LEFT JOIN gv$process p

ON p.addr = s.paddr

AND p.inst_id = s.inst_id

WHERE s.sql_id IN (

'5z0s7q1a976mp',

'8qf0gsw8scbr',

'a4gzfqk1c25n',

'daphqlqsnymv8',

'0bu1jgc94rg3fj'

)

OR s.prev_sql_id IN (

'5z0s7q1a976mp',

'8qf0gsw8scbr',

'a4gzfqk1c25n',

'daphqlqsnymv8',

'0bu1jgc94rg3fj'

)

ORDER BY s.inst_id, s.sid;

SELECT

request_id,

user_concurrent_program_name,

concurrent_program_name,

actual_start_date,

actual_completion_date,

ROUND((actual_completion_date - actual_start_date) * 86400, 0) AS duration_sec,

phase_code,

status_code,

user_name

FROM fnd_concurrent_requests_vl

WHERE actual_start_date BETWEEN TO_DATE('2026-07-24', 'YYYY-MM-DD') AND TO_DATE('2026-07-28 16:47', 'YYYY-MM-DD HH24:MI')

ORDER BY actual_start_date DESC

FETCH FIRST 50 ROWS ONLY;

SELECT

request_id,

user_concurrent_program_name,

concurrent_program_name,

actual_start_date,

actual_completion_date,

ROUND((actual_completion_date - actual_start_date) * 86400, 0) AS duration_sec,

phase_code,

status_code,

user_name

FROM fnd_concurrent_requests_vl

WHERE actual_start_date BETWEEN TO_DATE('2026-07-24', 'YYYY-MM-DD') AND TO_DATE('2026-07-28 16:47', 'YYYY-MM-DD HH24:MI')

ORDER BY actual_start_date DESC

FETCH FIRST 50 ROWS ONLY;

-- Find which Workflow event subscription is triggered by WSHPSRS

SELECT

event_filter_guid,

event_name,

owner_name,

owner_tag,

function_name,

status

FROM wf_event_subscriptions

WHERE LOWER(event_name) LIKE '%pick%'

OR LOWER(event_name) LIKE '%ship%'

OR LOWER(function_name) LIKE '%navigation%'

ORDER BY event_name;

SELECT TRUNC(creation_date, 'YYYY') AS creation_year,

file_content_type,

COUNT(*) AS row_count

FROM fnd_lobs

WHERE program_name IS NULL

AND expiration_date IS NULL

GROUP BY TRUNC(creation_date, 'YYYY'),

file_content_type

ORDER BY creation_year DESC, row_count DESC;

Monday, July 27, 2026

arc

1. Check archive generation by hour

SET LINES 200

SET PAGES 100

SELECT TO_CHAR(first_time,'DD-MON-YYYY HH24') AS archive_hour,

COUNT(*) AS archive_count,

ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS size_gb

FROM v$archived_log

WHERE first_time >= SYSDATE - 3

AND dest_id = 1

GROUP BY TO_CHAR(first_time,'DD-MON-YYYY HH24')

ORDER BY archive_hour;

2. Check daily archive generation

SELECT TO_CHAR(first_time,'DD-MON-YYYY') AS archive_date,

COUNT(*) AS log_count,

ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS total_gb

FROM v$archived_log

WHERE first_time >= SYSDATE - 7

AND dest_id = 1

GROUP BY TO_CHAR(first_time,'DD-MON-YYYY')

ORDER BY MIN(first_time);

3. Find recently growing objects in APPS_TS_TX_IDX

SET LINES 220

SET PAGES 100

COLUMN owner FORMAT A20

COLUMN segment_name FORMAT A45

COLUMN partition_name FORMAT A30

COLUMN segment_type FORMAT A20

SELECT *

FROM (

SELECT owner,

segment_name,

partition_name,

segment_type,

ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb

FROM dba_segments

WHERE tablespace_name = 'APPS_TS_TX_IDX'

ORDER BY bytes DESC

)

WHERE ROWNUM <= 30;

4. Check recent extent allocation

This helps identify objects that received new extents recently, provided auditing or historical views are available:

SELECT owner,

segment_name,

segment_type,

partition_name,

COUNT(*) AS extent_count,

ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS allocated_gb

FROM dba_extents

WHERE tablespace_name = 'APPS_TS_TX_IDX'

GROUP BY owner,

segment_name,

segment_type,

partition_name

ORDER BY allocated_gb DESC

FETCH FIRST 30 ROWS ONLY;

5. Check active sessions generating redo

Run the first query, wait 10–15 minutes, and run it again. Sessions with the largest increase are likely generating the redo.

SELECT s.sid,

s.serial#,

s.username,

s.program,

s.module,

s.action,

s.sql_id,

ROUND(st.value / 1024 / 1024, 2) AS redo_mb

FROM v$session s

JOIN v$sesstat st

ON st.sid = s.sid

JOIN v$statname sn

ON sn.statistic# = st.statistic#

WHERE sn.name = 'redo size'

AND s.username IS NOT NULL

ORDER BY st.value DESC

FETCH FIRST 30 ROWS ONLY;

6. Check EBS concurrent requests running during the issue

SELECT r.request_id,

p.user_concurrent_program_name,

r.phase_code,

r.status_code,

r.actual_start_date,

r.actual_completion_date,

ROUND((NVL(r.actual_completion_date, SYSDATE)

- r.actual_start_date) * 24, 2) AS runtime_hours,

r.oracle_process_id

FROM apps.fnd_concurrent_requests r

JOIN apps.fnd_concurrent_programs_vl p

ON p.concurrent_program_id = r.concurrent_program_id

AND p.application_id = r.program_application_id

WHERE r.actual_start_date >= SYSDATE - 2

ORDER BY r.actual_start_date DESC;

7. Check tablespace usage

SELECT df.tablespace_name,

ROUND(df.total_gb, 2) AS total_gb,

ROUND(df.total_gb - fs.free_gb, 2) AS used_gb,

ROUND(fs.free_gb, 2) AS free_gb,

ROUND((df.total_gb - fs.free_gb) / df.total_gb * 100, 2) AS used_pct

FROM (

SELECT tablespace_name,

SUM(bytes) / 1024 / 1024 / 1024 AS total_gb

FROM dba_data_files

WHERE tablespace_name = 'APPS_TS_TX_IDX'

GROUP BY tablespace_name

) df

JOIN (

SELECT tablespace_name,

SUM(bytes) / 1024 / 1024 / 1024 AS free_gb

FROM dba_free_space

WHERE tablespace_name = 'APPS_TS_TX_IDX'

GROUP BY tablespace_name

) fs

ON fs.tablespace_name = df.tablespace_name;

Likely causes include a bulk data load, index rebuild, CREATE INDEX, heavy updates or deletes, statistics collection with index maintenance, clone/post-clone processing, interface imports, or a repeatedly failing concurrent program. The archive-

What to check next

1. Is debug logging enabled?

Check the profile options:

SELECT profile_option_name,

profile_option_value

FROM apps.fnd_profile_option_values v,

apps.fnd_profile_options p

WHERE v.profile_option_id = p.profile_option_id

AND p.profile_option_name LIKE 'AFLOG%';

Or from the EBS front end:

FND: Debug Log Enabled

FND: Debug Log Level

If enabled at Statement or Procedure, it can generate very large volumes of log records.

2. Which module is writing to FND_LOG_MESSAGES?

SELECT module,

COUNT(*) cnt

FROM applsys.fnd_log_messages

WHERE timestamp >= SYSDATE - 3

GROUP BY module

ORDER BY cnt DESC;

3. Which workflow events are busiest?

SELECT event_name,

COUNT(*)

FROM apps.wf_events

GROUP BY event_name

ORDER BY COUNT(*) DESC;

(If WF_EVENTS does not contain the required data, we can query the relevant runtime workflow tables instead.)

COL profile_level FOR A15

COL profile_option_value FOR A20

SELECT

DECODE(level_id,

10001,'SITE',

10002,'APPLICATION',

10003,'RESPONSIBILITY',

10004,'USER',

10005,'SERVER',

TO_CHAR(level_id)) profile_level,

level_value,

profile_option_value

FROM apps.fnd_profile_option_values v,

apps.fnd_profile_options p

WHERE v.profile_option_id = p.profile_option_id

AND p.profile_option_name = 'AFLOG_ENABLED';

Friday, June 19, 2026

PARAMETERES

COL con_name FOR A20

COL parameter_name FOR A40

COL value FOR A80

SELECT c.name con_name,

p.name parameter_name,

p.value

FROM v$parameter p,

v$containers c

WHERE p.con_id=c.con_id

AND p.name IN (

'db_name',

'db_unique_name',

'service_names',

'utl_file_dir',

'local_listener',

'remote_listener',

'db_create_file_dest',

'log_archive_dest_1'

)

ORDER BY c.name,p.name;

Oracle Apps DBA stuff

Thursday, August 13, 2026

ter

Thursday, August 6, 2026

Troubleshooting an Idle or Stuck Oracle E-Business Suite Forms Session

Troubleshooting an Idle or Stuck Oracle E-Business Suite Forms Session

1. Identify the Exact Database Session

2. Identify the EBS Application User

3. Check Whether the Session Is Blocking Others

4. Review the Current and Previous SQL

Retrieve Aged-Out SQL from AWR

5. Review Recent Wait History

6. Determine Whether the Issue Is Isolated or Systemic

7. Check the Relevant Database Object

8. Build an ASH Activity Timeline

9. Check for an Open Transaction

10. Terminate the Session Only After Validation

Recommended Troubleshooting Sequence

Wednesday, August 5, 2026

Oracle Apps DBA Cookbook

Oracle Apps DBA Cookbook

1. Scope & Target Environment

2. Recipe Numbering

3. Recipe Anatomy

4. Risk Flags

5. Master Index — 572 Recipes

Build Status

Chapter 02 — SQL Toolkit: The EBS Data Dictionary

Friday, July 31, 2026

ref

Wednesday, July 29, 2026

workdlow

Monday, July 27, 2026

arc

Friday, June 19, 2026

PARAMETERES

Search This Blog

Blog Archive