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.

Oracle Apps DBA stuff

Thursday, August 6, 2026