DBMS_SESSION is a toolbox for the current session. It can set an identifier for the end user behind a shared connection, change NLS settings from PL/SQL, return a unique session ID, check enabled roles, pause execution, and set values in an application context that SYS_CONTEXT can read.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_session.set_identifier('client id'); -- and clear_identifier
dbms_session.set_nls('parameter', 'value');
dbms_session.unique_session_id -- function
dbms_session.is_role_enabled('ROLE') -- function, boolean
dbms_session.sleep(seconds);
dbms_session.set_context('namespace', 'attribute', 'value');Common Session Tasks
Example:
begin
dbms_session.set_identifier('agent-042'); -- end-user identity for auditing
dbms_session.set_nls('nls_date_format', '''YYYY-MM-DD''');
dbms_output.put_line('client id: ' || sys_context('USERENV', 'CLIENT_IDENTIFIER'));
dbms_output.put_line('today: ' || sysdate);
dbms_output.put_line('session id: ' || dbms_session.unique_session_id);
dbms_output.put_line('DB_DEVELOPER_ROLE enabled? '
|| case when dbms_session.is_role_enabled('DB_DEVELOPER_ROLE')
then 'yes' else 'no' end);
dbms_session.sleep(0.5);
dbms_session.clear_identifier;
end;
/Output:
client id: agent-042 today: 2026-09-26 session id: 00DB4C6C0001 DB_DEVELOPER_ROLE enabled? yes PL/SQL procedure successfully completed.
- SET_IDENTIFIER stores the end user, here agent-042, in CLIENT_IDENTIFIER, where auditing and V$SESSION can see it. CLEAR_IDENTIFIER removes it at the end.
- SET_NLS changes the date format, so SYSDATE prints as YYYY-MM-DD. The value is quoted inside the string.
- UNIQUE_SESSION_ID returns an ID unique among the sessions currently connected.
- IS_ROLE_ENABLED confirms DB_DEVELOPER_ROLE is active, and SLEEP pauses for half a second.
Set an Application Context
An application context is a set of name and value pairs that only a trusted package can set. CREATE CONTEXT names the package, and the package calls SET_CONTEXT.
Example:
create or replace package nimbus_ctx_api is
procedure set_agent_desk (p_desk varchar2);
end;
/
create or replace package body nimbus_ctx_api is
procedure set_agent_desk (p_desk varchar2) is
begin
dbms_session.set_context('nimbus_app_ctx', 'desk', p_desk);
end;
end;
/
create context nimbus_app_ctx using nimbus_ctx_api;
exec nimbus_ctx_api.set_agent_desk('DXB-T3')
select sys_context('nimbus_app_ctx', 'desk') as desk from dual;Output:
Package NIMBUS_CTX_API compiled Package Body NIMBUS_CTX_API compiled Context NIMBUS_APP_CTX created. PL/SQL procedure successfully completed. DESK _________ DXB-T3
SYS_CONTEXT reads the value anywhere in the session, including in views and security policies. Calling DBMS_SESSION.SET_CONTEXT directly, outside NIMBUS_CTX_API, would fail, which is what makes contexts safe for security decisions. The example drops the context and the package afterward.
Things to Know
- Creating a context needs the CREATE ANY CONTEXT privilege, and a context belongs to the database, not to a schema.
- Use DBMS_SESSION.SLEEP rather than DBMS_LOCK.SLEEP, which needs an extra grant.
- Clear identifiers and contexts when a pooled connection is handed back, so the next user does not inherit them.
Related Guides
Conclusion
DBMS_SESSION sets client identifiers, NLS parameters, and application context values, and gives the session ID, enabled roles, and a sleep. Use it to make each session identify its real user and carry the settings your code and policies depend on.
