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

- Link: https://ultimasolution.pl/oracle-unique-session-id/
- Published: 2016-02-04T16:23:31+01:00
- Author: Karol Preiskorn

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+

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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#`.

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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

    ```EnlighterJSRAW
    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.

    ```EnlighterJSRAW
    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

 * [Oracle Database 19c DBMS_SESSION documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SESSION.html)
 * [Oracle Database 19c V$SESSION reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESSION.html)
 * [Oracle Database 19c GV$SESSION reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/GV-SESSION.html)