Troubleshooting APEX Sessions

The Session Correlation Chain
APEX Session → ORDS → Database Session

• APEX Session ID (APP_SESSION) - The unique APEX session identifier visible in the browser URL and stored in APEX_WORKSPACE_ACTIVITY_LOG
• ORDS Client Identifier - ORDS sets the database session's CLIENT_IDENTIFIER to the APEX session ID
• Database Session - The actual Oracle database session (V$SESSION)

Practical Correlation Techniques

Method 1: Using V$SESSION (Real-time)

SELECT 
   s.sid,
   s.serial#,
   s.username,
   s.osuser,
   s.machine,
   s.program,
   s.module,
   s.action,
   s.client_identifier,  -- This contains the APEX Session ID
   s.client_info,
   s.logon_time,
   s.status,
   s.sql_id,
   s.prev_sql_id
FROM v$session s
WHERE s.client_identifier = :APP_SESSION  -- Your APEX session ID
  OR s.module LIKE 'APEX:%';

Method 2: Using APEX_WORKSPACE_ACTIVITY_LOG (Historical)

SELECT 
   apex_session_id,
   apex_user,
   application_id,
   page_id,
   view_date,
   elapsed_time,
   userid,
   ip_address,
   user_agent,
   apex_session_id  -- Maps to CLIENT_IDENTIFIER in V$SESSION
FROM apex_workspace_activity_log
WHERE apex_session_id = :APP_SESSION
ORDER BY view_date DESC;

Method 3: Join APEX Activity with Database Sessions 

SELECT 
   aal.apex_session_id,
   aal.apex_user,
   aal.application_id,
   aal.page_id,
   s.sid,
   s.serial#,
   s.sql_id,
   s.status,
   s.machine,
   s.program,
   s.logon_time
FROM apex_workspace_activity_log aal
LEFT JOIN v$session s 
   ON s.client_identifier = TO_CHAR(aal.apex_session_id)
WHERE aal.apex_user = :USERNAME
 AND aal.view_date > SYSDATE - 1/24  -- Last hour
ORDER BY aal.view_date DESC;

ORDS-Specific Correlation

Check ORDS Connection Pool Sessions

-- Find all ORDS pool sessions
SELECT 
   sid,
   serial#,
   username,
   program,
   module,
   client_identifier,
   status,
   sql_id
FROM v$session
WHERE program LIKE 'JDBC%'
 AND username = 'ORDS_PUBLIC_USER'  -- Or your ORDS schema
 AND client_identifier IS NOT NULL;

Track Active APEX Sessions via ORDS

-- Active APEX sessions going through ORDS
SELECT 
   s.sid,
   s.serial#,
   s.client_identifier AS apex_session_id,
   s.module,
   s.action,
   s.sql_id,
   sa.sql_text,
   s.last_call_et,
   s.status
FROM v$session s
LEFT JOIN v$sqlarea sa ON s.sql_id = sa.sql_id
WHERE s.module LIKE 'APEX:%'
 AND s.client_identifier IS NOT NULL
ORDER BY s.last_call_et DESC;

Setting Context for Better Tracking

In your APEX application, you can enhance tracking by setting additional context:

-- In an APEX Application Process or Page Load
BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE(
       module_name => 'APEX:' || :APP_ID || ':' || :APP_PAGE_ID,
       action_name => :APP_USER
   );
   
   DBMS_SESSION.SET_IDENTIFIER(:APP_SESSION);
   
   -- Optional: Set client info
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(
       'User:' || :APP_USER || ',Page:' || :APP_PAGE_ID
   );
END;

Monitoring Query for DBAs 

-- Complete correlation view for troubleshooting
SELECT 
   s.sid,
   s.serial#,
   s.username AS db_user,
   s.client_identifier AS apex_session,
   SUBSTR(s.module, 6, INSTR(s.module, ':', 6) - 6) AS app_id,
   s.action AS apex_user,
   s.sql_id,
   s.blocking_session,
   s.event,
   s.wait_time,
   s.seconds_in_wait,
   s.status,
   sa.sql_text
FROM v$session s
LEFT JOIN v$sqlarea sa ON s.sql_id = sa.sql_id
WHERE s.module LIKE 'APEX:%'
 AND s.username != 'SYS'
ORDER BY s.last_call_et DESC;

Key Identifiers Summary

• CLIENT_IDENTIFIER = APEX Session ID (most reliable correlation)
• MODULE = APEX:<app_id>:<page_id>
• ACTION = APEX username (when set properly)
• PROGRAM = JDBC Thin Client (for ORDS connections)
• USERNAME = ORDS parsing schema (often ORDS_PUBLIC_USER or workspace schema)