Oracle 19c+ Unique Session ID: DBMS_SESSION, SYS_CONTEXT, and RAC

Find and trace Oracle 19c+ sessions with DBMS_SESSION.UNIQUE_SESSION_ID, SYS_CONTEXT, V$SESSION, GV$SESSION, client identifiers, modules, actions, and RAC-aware troubleshooting examples.

Find and trace Oracle 19c+ sessions with DBMS_SESSION.UNIQUE_SESSION_ID, SYS_CONTEXT, V$SESSION, GV$SESSION, client identifiers, modules, actions, and RAC-aware troubleshooting examples.
Photo by Gabriel Heinzer

When troubleshooting Oracle Database 19c or later, a session identifier helps connect application logs, database traces, SQL Monitor data, and V$SESSION rows. There is no single identifier that answers every question. The correct value depends on whether you need the current physical database session, a RAC instance, a pooled application request, or a durable business correlation ID.

This guide explains how to identify and trace Oracle 19c+ sessions with DBMS_SESSION.UNIQUE_SESSION_ID, SYS_CONTEXT, V$SESSION, and GV$SESSION. It also shows how to set module, action, and client identifiers correctly in connection-pool applications.

Which Oracle 19c+ identifier should you use?

  • DBMS_SESSION.UNIQUE_SESSION_ID identifies the current Oracle session for diagnostic use.
  • SID identifies a session inside one database instance.
  • SERIAL# distinguishes a later session that reuses the same SID.
  • INST_ID is required to locate a session across RAC instances.
  • CLIENT_IDENTIFIER identifies an application user or request context when the application sets it.
  • MODULE and ACTION identify the application operation.
  • SYS_GUID() can generate a request correlation token, but it is not the Oracle session ID.

A physical session can be reused by a connection pool. Therefore, do not use a session identifier as a permanent user ID, request ID, or audit record key without adding an application-generated correlation value.

Get the current session ID in Oracle 19c+

SELECT DBMS_SESSION.UNIQUE_SESSION_ID AS unique_session_id,
       SYS_CONTEXT('USERENV', 'SID') AS sid,
       SYS_CONTEXT('USERENV', 'INSTANCE') AS instance_number,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name,
       SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') AS client_identifier
FROM dual;

DBMS_SESSION.UNIQUE_SESSION_ID is convenient inside PL/SQL. The USERENV values provide additional context that is easier to query and log.

Display the current session details from V$SESSION

Use SID and SERIAL# together. A SID alone is not a stable session reference because Oracle can reuse it after a session disconnects.

SELECT sid,
       serial#,
       audsid,
       username,
       status,
       service_name,
       module,
       action,
       client_identifier,
       sql_id,
       prev_sql_id,
       logon_time,
       machine,
       program
FROM v$session
WHERE sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID'));

Reading V$SESSION requires the appropriate catalog or select privileges. If the application user cannot read the view, collect the session context inside the application schema and let a monitoring account perform the database-wide query.

Use GV$SESSION for Oracle RAC

In Real Application Clusters, search GV$SESSION and include INST_ID. The practical physical-session key is usually INST_ID + SID + SERIAL#.

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       service_name,
       module,
       action,
       client_identifier,
       sql_id,
       event,
       wait_class,
       last_call_et
FROM gv$session
WHERE client_identifier = :client_identifier
ORDER BY inst_id, sid;

Do not assume the same SID means the same session on every RAC instance. Include INST_ID in dashboards, support queries, and kill-session procedures.

Build a readable physical session key

SELECT inst_id || ':' || sid || ':' || serial# AS physical_session_key,
       username,
       service_name,
       module,
       action,
       client_identifier
FROM gv$session
WHERE sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID'))
  AND inst_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'INSTANCE'));

This key is useful for a short-lived diagnostic event. It is not a durable identifier because the session can disconnect and the SID and serial number can later be reused.

Set MODULE and ACTION with DBMS_APPLICATION_INFO

Set the module and action at an application boundary before executing the business operation. These values become visible in V$SESSION, SQL tracing, and many performance diagnostics.

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'order-api',
    action_name => 'create-order'
  );

  -- Execute the business operation here.
  NULL;

  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
    RAISE;
END;
/

Keep module and action values short, stable, and useful for grouping. Do not put passwords, tokens, full SQL statements, or unrestricted request payloads in these fields.

Set CLIENT_IDENTIFIER with DBMS_SESSION

CLIENT_IDENTIFIER is useful for associating a physical Oracle session with an application user, tenant, or request. It is especially important when many application users share a connection pool.

BEGIN
  DBMS_SESSION.SET_IDENTIFIER(
    client_id => 'tenant-42:user-9001:request-7f31'
  );

  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'billing-api',
    action_name => 'read-invoice'
  );
END;
/

Only place trusted, bounded identifiers in the client identifier. Validate length and character content in the application before sending it to the database.

Reset pooled-session context

A connection pool returns the same physical session to different requests. Clear or overwrite context when the request ends; otherwise, the next request can appear to belong to the previous user.

BEGIN
  DBMS_SESSION.CLEAR_IDENTIFIER;
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;
/

The pool integration should run this reset in both success and error paths. A database-side reset is not a replacement for correct pool configuration.

Generate an application request correlation ID

Use a separate request ID when the goal is to trace one HTTP request, message, or job across several database sessions.

DECLARE
  l_request_id VARCHAR2(32);
BEGIN
  l_request_id := RAWTOHEX(SYS_GUID());

  DBMS_SESSION.SET_IDENTIFIER(l_request_id);
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'invoice-api',
    action_name => 'post-invoice'
  );

  DBMS_OUTPUT.PUT_LINE('request_id=' || l_request_id);
END;
/

Pass an application-generated correlation ID when possible so the same value appears in the web log, message log, and database log. SYS_GUID() is a token generator, not a proof of session identity.

Log session context in PL/SQL

CREATE TABLE app_session_event_log (
  event_time        TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  request_id        VARCHAR2(64),
  session_id        VARCHAR2(64),
  sid               NUMBER,
  serial_number     NUMBER,
  instance_number   NUMBER,
  database_user     VARCHAR2(128),
  client_identifier VARCHAR2(256),
  module_name       VARCHAR2(128),
  action_name       VARCHAR2(128),
  event_name        VARCHAR2(128)
);

INSERT INTO app_session_event_log (
  request_id,
  session_id,
  sid,
  serial_number,
  instance_number,
  database_user,
  client_identifier,
  module_name,
  action_name,
  event_name
)
SELECT :request_id,
       DBMS_SESSION.UNIQUE_SESSION_ID,
       s.sid,
       s.serial#,
       s.inst_id,
       SYS_CONTEXT('USERENV', 'SESSION_USER'),
       SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'),
       SYS_CONTEXT('USERENV', 'MODULE'),
       SYS_CONTEXT('USERENV', 'ACTION'),
       :event_name
FROM gv$session s
WHERE s.sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID'))
  AND s.inst_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'INSTANCE'));

For high-volume logging, use a bounded and indexed design. Avoid inserting every session lookup into a permanent table unless the retention and performance impact are understood.

Find the SQL currently used by a session

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       s.client_identifier,
       q.sql_text
FROM gv$session s
LEFT JOIN gv$sql q
  ON q.inst_id = s.inst_id
 AND q.sql_id = s.sql_id
WHERE s.inst_id = :inst_id
  AND s.sid = :sid
  AND s.serial# = :serial_number;

A session may be idle when you query it, so SQL_ID can be null or may refer to a different moment than the application error. Use timestamps and application correlation IDs when reconstructing a timeline.

Safely terminate a problem session

Only a suitably privileged operations account should terminate sessions. Confirm the full RAC-aware key before issuing a kill command.

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       machine,
       program,
       module,
       action
FROM gv$session
WHERE inst_id = :inst_id
  AND sid = :sid
  AND serial# = :serial_number;

ALTER SYSTEM KILL SESSION ':sid,:serial_number,@:inst_id' IMMEDIATE;

Never build the ALTER SYSTEM command from unvalidated user input. Verify the target session and follow the organization’s incident procedure before killing it.

Common Oracle 19c+ session-ID mistakes

  • Using SID alone instead of SID plus serial number.
  • Ignoring INST_ID in RAC.
  • Treating DBMS_SESSION.UNIQUE_SESSION_ID as a permanent audit identifier.
  • Using a physical session ID as an HTTP request ID.
  • Failing to clear CLIENT_IDENTIFIER in a connection pool.
  • Putting secrets or complete SQL text into MODULE or ACTION.
  • Querying V$SESSION without the privileges required by the monitoring account.
  • Assuming SQL_ID is always available when a session is idle.
  • Logging every session lookup without retention, indexes, or volume controls.

Oracle 19c+ session troubleshooting checklist

  1. Decide whether you need a physical session key, RAC key, application request ID, or business correlation ID.
  2. Collect SID, SERIAL#, and INST_ID for physical-session diagnostics.
  3. Collect DBMS_SESSION.UNIQUE_SESSION_ID and SYS_CONTEXT values inside PL/SQL.
  4. Set MODULE and ACTION at the application operation boundary.
  5. Set CLIENT_IDENTIFIER for pooled-connection user or request context.
  6. Reset pooled-session context after every request.
  7. Use GV$SESSION and GV$SQL for RAC-wide investigation.
  8. Correlate SQL_ID with application logs, but account for idle sessions and changing SQL.
  9. Protect session data and do not expose credentials or unrestricted user input.
  10. Retest the issue with a fresh request correlation ID.

Summary

For Oracle 19c and later, use DBMS_SESSION.UNIQUE_SESSION_ID and SYS_CONTEXT for current-session diagnostics, SID plus SERIAL# plus INST_ID for RAC-aware physical-session identification, and CLIENT_IDENTIFIER with MODULE and ACTION for application tracing. Use SYS_GUID or an application-generated value for request correlation, and reset connection-pool context so one user’s diagnostic data is not attached to another user.

Oracle documentation references

2016-02-04sql Off Comments off on Oracle 19c+ Unique Session ID: DBMS_SESSION, SYS_CONTEXT, and RAC1550