Oracle | SYS

Oracle SYS_CONTEXT Version 23ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Note: USERENV is an Oracle provided namespace that describes the current session.

Data Dictionary Objects

DBA_EDITION	V$PARAMETER	V$SESSION
V$DATABASE

Syntax SELECT sys_context('<namespace>', '<parameter>', <length>); SYS_CONTEXT(namespace IN VARCHAR2, attribute IN VARCHAR2) RETURN VARCHAR; SYS_CONTEXT(namespace IN VARCHAR2, attribute IN VARCHAR2, newoptional IN VARCHAR2) RETURN VARCHAR2;

ACTION

Retrieves the value for the current session from v$session.action SYS_CONTEXT('USERENV', 'ACTION')

SELECT sys_context('USERENV', 'ACTION');

      

      exec dbms_application_info.set_action('INSERTING');

      

      SELECT sys_context('USERENV', 'ACTION');

APPLICATION_NAME

Retrieves the name of the application installed in the current application container SYS_CONTEXT('USERENV', 'APPLICATION_NAME')

ALTER PLUGGABLE DATABASE APPLICATION uw_app BEGIN INSTALL '1.0';

      

      SELECT sys_context('USERENV', 'APPLICATION_NAME');

      

      SYS_CONTEXT('USERENV','AUTHENTICATION_NAME')

      ---------------------------------------------

      UW_APP

AUDITED_CURSORID

Returns the cursor ID of the SQL that triggered the audit. Will return NULL with FGA. SYS_CONTEXT('USERENV', 'AUDITED_CURSORID')

SELECT sys_context('USERENV', 'AUDITED_CURSORID');

AUTHENTICATED_IDENTITY

Returns the identity used in logon authentication SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY')

SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY');

      

      SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')

      -----------------------------------------------

      uwclass

AUTHENTICATION_DATA

Data being used to authenticate the login user. For X.503 sessions, returns the context of the certificate in HEX2 format. SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA')

SELECT sys_context('USERENV', 'AUTHENTICATION_DATA');

AUTHENTICATION_METHOD

Returns the method of authentication SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD')

SELECT sys_context('USERENV', 'AUTHENTICATION_METHOD');

      

      SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')

      ----------------------------------------------

      PASSWORD

BG_JOB_ID

Job ID of the current session if it was established by an Oracle background process, else NULL SYS_CONTEXT('USERENV', 'BG_JOB_ID')

SELECT sys_context('USERENV', 'BG_JOB_ID');

CDB_DOMAIN (new 23ai)

The DB_DOMAIN of the CDB and is the same for all associated PDBs SYS_CONTEXT('USERENV', 'CDB_NAME')

SELECT sys_context('USERENV', 'CDB_DOMAIN');

      

      SYS_CONTEXT('USERENV','CDB_DOMAIN')

      ------------------------------------

CDB_NAME

Name of the container database SYS_CONTEXT('USERENV', 'CDB_NAME')

SELECT sys_context('USERENV', 'CDB_NAME');

      

      SYS_CONTEXT('USERENV','CDB_NAME')

      ---------------------------------

      FREE

CLIENT_IDENTIFIER

Returns an identifier set by DBMS_SESSION.SET_IDENTIFIER. Can be used by to identify lightweight application users who authenticate as the same user. SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')

SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER');

      

      exec dbms_session.set_identifier(USER || ' ' || SYSTIMESTAMP);

      

      SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER');

CLIENT_INFO

Returns the value from v$session.client_info that can bet set using DBMS_APPLICATION_INFO SYS_CONTEXT('USERENV', 'CLIENT_INFO')

SELECT sys_context('USERENV', 'CLIENT_INFO');

      

      exec dbms_application_info.set_client_info('TEST');

      

      SELECT sys_context('USERENV', 'CLIENT_INFO');

CLIENT_PROGRAM_NAME

Name of the program used for the database session SYS_CONTEXT('USERENV', 'CLIENT_PROGRAM_NAME')

 conn / as sysdba

      

      SELECT sys_context('USERENV', 'CLIENT_PROGRAM_NAME');

      

      SYS_CONTEXT('USERENV','CLIENT_PROGRAM_NAME')

      ---------------------------------------------

      sqlplus.exe

CLOUD_SERVICE (new 23ai)

Only valid in cloud implementations. Returns DWCS for Oracle Autonomous Database, ATP for Autonomous Transaction Processing, JDCS for Autonomous JSON Database SYS_CONTEXT('USERENV', 'CLIENT_PROGRAM_NAME')

conn / as sysdba

      

      SELECT sys_context('USERENV', 'CLOUD_SERVICE');

      

      SYS_CONTEXT('USERENV','CLOUD_SERVICE')

      ---------------------------------------

      JDCS

CON_ID

Container Identifier SYS_CONTEXT('USERENV', 'CON_ID')

conn / as sysdba

      

      SELECT sys_context('USERENV', 'CON_ID');

      

      SYS_CONTEXT('USERENV','CON_ID')

      -------------------------------

      4

CON_NAME

Container name SYS_CONTEXT('USERENV', 'CON_NAME')

conn / as sysdba

      

      SELECT sys_context('USERENV', 'CON_NAME');

      

      SYS_CONTEXT('USERENV','CON_NAME')

      ---------------------------------

      CDB$ROOT

      

      conn uwclass/uwclass@orabase

      

      SYS_CONTEXT('USERENV','CON_NAME')

      ---------------------------------

      ORABASE

CURRENT_BIND

The bind variables for fine-grained auditing SYS_CONTEXT('USERENV', 'CURRENT_BIND')

TBD

CURRENT_EDITION_ID

The numeric identifier of the current edition SYS_CONTEXT('USERENV', 'CURRENT_EDITION_ID')

SELECT sys_context('USERENV', 'CURRENT_EDITION_ID');

      

      SYS_CONTEXT('USERENV','CURRENT_EDITION_ID')

      --------------------------------------------

      131

CURRENT_EDITION_NAME

The name of the current edition SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME')

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME');

      

      SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')

      ---------------------------------------------

      ORA$BASE

CURRENT_SCHEMA

Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement. SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')

SELECT sys_context('USERENV', 'CURRENT_SCHEMA');

      

      SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

      ---------------------------------------

      UWCLASS

CURRENT_SCHEMAID

Identifier of the default schema being used in the current session SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID')

SELECT sys_context('USERENV', 'CURRENT_SCHEMAID');

      

      SYS_CONTEXT('USERENV','CURRENT_SCHEMAID')

      -----------------------------------------

      102

      

      SELECT user#

      FROM sys.user$

      WHERE name = USER;

CURRENT_SQL

Returns the first 4K bytes of the current SQL that triggered the fine-grained auditing (FGA) event SYS_CONTEXT('USERENV', 'CURRENT_SQL')

TBD

CURRENT_SQLn

CURRENT_SQLn attributes return subsequent 4K-byte increments, where n can be an integer from 1 to 7, inclusive if FGA is triggered SYS_CONTEXT('USERENV', 'CURRENT_SQLn')

TBD

CURRENT_SQL_LENGTH

The length of the current SQL that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers where it is located SYS_CONTEXT('USERENV', 'CURRENT_SQL_LENGTH')

TBD

CURRENT_USER

The name of the database user whose privileges are currently active SYS_CONTEXT('USERENV', 'CURRENT_USER')

SELECT sys_context('USERENV', 'CURRENT_USER');

      

      SYS_CONTEXT('USERENV','CURRENT_USER')

      -------------------------------------

      UWCLASS

CURRENT_USERID

The identifier of the database user whose privileges are currently active SYS_CONTEXT('USERENV', 'CURRENT_USERID')

SELECT sys_context('USERENV', 'CURRENT_USERID');

      

      SYS_CONTEXT('USERENV','CURRENT_USERID')

      ---------------------------------------

      102

DATABASE_ROLE

The database role is one of the following: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY, SNAPSHOT STANDBY SYS_CONTEXT('USERENV', 'DATABASE_ROLE')

SELECT sys_context('USERENV', 'DATABASE_ROLE');

      

      SYS_CONTEXT('USERENV','DATABASE_ROLE')

      --------------------------------------

      PRIMARY

DB_DOMAIN

Domain of the database as specified in the DB_DOMAIN initialization parameter SYS_CONTEXT('USERENV', 'DB_DOMAIN')

SELECT sys_context('USERENV', 'DB_DOMAIN');

DB_NAME

Name of the database as specified in the DB_NAME initialization parameter. SYS_CONTEXT('USERENV', 'DB_NAME')

SELECT sys_context('USERENV', 'DB_NAME');

      

      SYS_CONTEXT('USERENV','DB_NAME')

      --------------------------------

      TEST21DB

      

      SELECT name, value

      FROM gv$parameter

      where name LIKE 'db%name';

DB_SUPPLEMENTAL_LOG_LEVEL

If supplemental logging is enabled, returns a string containing the list of enabled supplemental logging levels. Possible values are: ALL_COLUMN, FOREIGN_KEY, MINIMAL, PRIMARY_KEY, PROCEDURAL, and UNIQUE_INDEX: Else NULL SYS_CONTEXT('USERENV', 'CON_ID')

conn / as sysdba

      

      SELECT sys_context('USERENV', 'DB_SUPPLEMENTAL_LOG_LEVEL');

      

      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

      

      SELECT sys_context('USERENV', 'DB_SUPPLEMENTAL_LOG_LEVEL');

      

      SYS_CONTEXT('USERENV','DB_SUPPLEMENTAL_LOG_LEVEL')

      --------------------------------------------------

      MINIMAL

      

      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

      

      SELECT sys_context('USERENV', 'DB_SUPPLEMENTAL_LOG_LEVEL');

      

      SYS_CONTEXT('USERENV','DB_SUPPLEMENTAL_LOG_LEVEL')

      --------------------------------------------------

      MINIMAL, UNIQUE INDEX

DB_UNIQUE_NAME

Name of the database as specified in the DB_UNIQUE_NAME initialization parameter SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')

SELECT sys_context('USERENV', 'DB_UNIQUE_NAME');

      

      SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')

      ---------------------------------------

      orabasexxiii

      

      SELECT name, value

      FROM  v$parameter

      where name LIKE 'db%name';

      

      NAME            VALUE

      --------------- -------------------------------

      db_name         orabase

      db_unique_name  orabasexxiii

DBLINK_INFO

Returns the source of a DB_LINK session SYS_CONTEXT('USERENV', 'DBLINK_INFO')

SELECT sys_context('USERENV', 'DBLINK_INFO');

DRAIN_STATUS (new 23ai)

The draining status of the current session. Returns DRAINING if the session is a candidate for Database Resident Connection Pooling (DRCP) draining else NONE SYS_CONTEXT('USERENV', 'DRAIN_STATUS')

SELECT sys_context('USERENV', 'DRAIN_STATUS');

      

      SYS_CONTEXT('USERENV','DRAIN_STATUS')

      ---------------------------------------

      NONE

ENTRYID

The available auditing entry identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to true. SYS_CONTEXT('USERENV', 'ENTRYID')

SELECT sys_context('USERENV', 'ENTRYID');

ENTERPRISE_IDENTITY

Returns the user's enterprise-wide identity SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY')

SELECT sys_context('USERENV', 'ENTERPRISE_IDENTITY');

FG_JOB_ID

Job ID of the current session if it was established by a client foreground process else NULL SYS_CONTEXT('USERENV', 'FG_JOB_ID')

SELECT sys_context('USERENV', 'FG_JOB_ID');

GLOBAL_CONTEXT_MEMORY

The number used in the System Global Area by the globally accessed context SYS_CONTEXT('USERENV', 'GLOBAL_CONTEXT_MEMORY')

SELECT sys_context('USERENV', 'GLOBAL_CONTEXT_MEMORY');

      

      SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY')

      -----------------------------------------------

      0

GLOBAL_UID

Returns the global user ID from Oracle Internet Directory for Enterprise User Security (EUS) login: Else NULL SYS_CONTEXT('USERENV', 'GLOBAL_UID')

SELECT sys_context('USERENV', 'GLOBAL_UID');

HOST

Name of the host machine from which the client has connected SYS_CONTEXT('USERENV', 'HOST')

SELECT sys_context('USERENV', 'HOST');

      

      SYS_CONTEXT('USERENV','HOST')

      -----------------------------

      WORKGROUP\PERRITO4

IDENTIFICATION_TYPE

Returns the way the user's schema was created in the database. Specifically, the IDENTIFIED clause of the CREATE/ALTER USER DDL SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE')

SELECT sys_context('USERENV', 'IDENTIFICATION_TYPE');

      

      SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')

      ---------------------------------------------

      LOCAL

INSTANCE

The instance identification number of the instance to which the session is connected SYS_CONTEXT('USERENV', 'INSTANCE')

SELECT sys_context('USERENV', 'INSTANCE');

INSTANCE_NAME

The name of the instance to which the session is connected SYS_CONTEXT('USERENV', 'INSTANCE_NAME')

SELECT sys_context('USERENV', 'INSTANCE_NAME');

IP_ADDRESS

IP address of the NIC from which the client is connected SYS_CONTEXT('USERENV', 'IP_ADDRESS')

SELECT sys_context('USERENV', 'IP_ADDRESS');

      

      SYS_CONTEXT('USERENV', 'IP_ADDRESS')

      ------------------------------------

      141.204.244.96

IS_APPLY_SERVER

Returns TRUE if queried from within a SQL Apply server in a logical standby database. Otherwise, returns FALSE SYS_CONTEXT('USERENV', 'IS_APPLY_SERVER')

SELECT sys_context('USERENV', 'IS_APPLY_SERVER');

      

      SYS_CONTEXT('USERENV','IS_APPLY_SERVER')

      ----------------------------------------

      FALSE

IS_DG_ROLLING_UPGRADE

Returns TRUE if a rolling upgrade of the database software in a Data Guard configuration, initiated by way of the DBMS_ROLLING package, is active. Otherwise, returns FALSE. SYS_CONTEXT('USERENV', 'IS_DG_ROLLING_UPGRADE')

SELECT sys_context('USERENV', 'IS_DG_ROLLING_UPGRADE');

      

      SYS_CONTEXT('USERENV','IS_DG_ROLLING_UPGRADE')

      ----------------------------------------------

      FALSE

ISDBA

TRUE if the session is SYS SYS_CONTEXT('USERENV', 'ISDBA')

SELECT sys_context('USERENV', 'ISDBA');

      

      SYS_CONTEXT('USERENV','ISDBA')

      -------------------------------

      TRUE

LANG

The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter. SYS_CONTEXT('USERENV', 'LANG')

SELECT sys_context('USERENV', 'LANG');

      

      SYS_CONTEXT('USERENV','LANG')

      ------------------------------

      US

LANGUAGE

The language and territory currently used by your session, along with the database character set, in the form: language_territory.characterset. SYS_CONTEXT('USERENV', 'LANGUAGE')

SELECT sys_context('USERENV', 'LANGUAGE');

      

      SYS_CONTEXT('USERENV','LANGUAGE')

      ----------------------------------

      AMERICAN_AMERICA.AL32UTF8

LDAP_SERVER_TYPE

Returns the configured LDAP server type, one of OID, AD(Active Directory), OID_G, or OPENLDAP SYS_CONTEXT('USERENV', 'LDAP_SERVER_TYPE')

SELECT sys_context('USERENV', 'LDAP_SERVER_TYPE');

MODULE

The application name (module) set through DBMS_APPLICATION_INFO SYS_CONTEXT('USERENV', 'MODULE')

SELECT sys_context('USERENV', 'MODULE');

NETWORK_PROTOCOL

Network protocol being used for communication, as specified in the 'PROTOCOL=protocol' portion of the connect string SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL')

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL');

      

      SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL')

      -------------------------------------------

      tcp

NLS_CALENDAR

The current calendar of the current session SYS_CONTEXT('USERENV', 'NLS_CALENDAR')

SELECT sys_context('USERENV', 'NLS_CALENDAR');

      

      SYS_CONTEXT('USERENV','NLS_CALENDAR')

      -------------------------------------

      GREGORIAN

NLS_CURRENCY

The currency of the current session SYS_CONTEXT('USERENV', 'NLS_CURRENCY')

SELECT sys_context('USERENV', 'NLS_CURRENCY');

      

      SYS_CONTEXT('USERENV','NLS_CURRENCY')

      -------------------------------------

      $

NLS_DATE_FORMAT

The date format for the session SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT')

SELECT sys_context('USERENV', 'NLS_DATE_FORMAT');

      

      SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')

      ----------------------------------------

      DD-MON-YYYY HH24:MI:SS

NLS_DATE_LANGUAGE

The language used for expressing dates SYS_CONTEXT('USERENV', 'NLS_LANGUAGE')

SELECT sys_context('USERENV', 'NLS_DATE_LANGUAGE');

      

      SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE')

      ------------------------------------------

      AMERICAN

NLS_SORT

BINARY or the linguistic sort basis SYS_CONTEXT('USERENV', 'NLS_SORT')

SELECT sys_context('USERENV', 'NLS_SORT');

      

      SYS_CONTEXT('USERENV','NLS_SORT')

      ---------------------------------

      BINARY

NLS_TERRITORY

The territory of the current session SYS_CONTEXT('USERENV', 'NLS_TERRITORY')

SELECT sys_context('USERENV', 'NLS_TERRITORY');

      

      SYS_CONTEXT('USERENV','NLS_TERRITORY')

      --------------------------------------

      AMERICA

ORACLE_HOME

Returns the value of $ORACLE_HOME SYS_CONTEXT('USERENV', 'ORACLE_HOME')

SELECT sys_context('USERENV', 'ORACLE_HOME');

      

      SYS_CONTEXT('USERENV','ORACLE_HOME')

      -------------------------------------

      /u01/app/oracle/product/21.1.0/dbhome_1

OS_USER

Operating system username of the client process that initiated the database session SYS_CONTEXT('USERENV', 'OS_USER')

SELECT sys_context('USERENV', 'OS_USER');

      

      SYS_CONTEXT('USERENV','OS_USER')

      --------------------------------

      perrito5\oracle

PID

Oracle Process Identifier SYS_CONTEXT('USERENV', 'PID')

SELECT UNIQUE sid FROM v$mystat;

      

         SID

      ------

         261

      

      SELECT process FROM v$session WHERE sid=261;

      

      PROCESS

      ----------

      1680:4228

      

      SELECT sys_context('USERENV', 'PID');

      

      SYS_CONTEXT('USERENV','PID')

      ----------------------------

      1680:4228

PLATFORM_SLASH

In theory, returns the forward or back-slash for the operating system environment, unfortunately it returns the *nix slash on Windows servers. SYS_CONTEXT('USERENV', 'PLATFORM_SLASH')

SELECT sys_context('USERENV', 'PLATFORM_SLASH');

      

      SYS_CONTEXT('USERENV','PLATFORM_SLASH')

      ---------------------------------------

      /

POLICY_INVOKER

The invoker of row-level security (RLS) policy functions SYS_CONTEXT('USERENV', 'POLICY_INVOKER')

SELECT sys_context('USERENV', 'POLICY_INVOKER');

PROXY_ENTERPRISE_IDENTITY

Returns the Oracle Internet Directory DN when the proxy user is an enterprise user SYS_CONTEXT('USERENV', 'PROXY_ENTERPRISE_IDENTITY')

SELECT sys_context('USERENV', 'PROXY_ENTERPRISE_IDENTITY');

      

      SYS_CONTEXT('USERENV', 'PROXY_ENTERPRISE_IDENTITY')

      ----------------------------------------------------

      dmorgan@dbsecworx.com

PROXY_USER

Name of the database user who opened the current session on behalf of the SESSION_USER SYS_CONTEXT('USERENV', 'PROXY_USER')

conn dam42z[m12345]@oratest

      

      SELECT sys_context('USERENV', 'PROXY_USER');

      

      SYS_CONTEXT('USERENV', 'PROXY_USER')

      -------------------------------------

      DM42Z

PROXY_USERID

Identifier of the database user who opened the current session on behalf of SESSION_USER SYS_CONTEXT('USERENV', 'PROXY_USERID')

conn dam42z[m12345]@oratest

      

      SELECT sys_context('USERENV', 'PROXY_USERID');

      

      SYS_CONTEXT('USERENV', 'PROXY_USERID')

      ---------------------------------------

      247

SCHEDULER_JOB

Returns Y if the current session belongs to a foreground job or background job. Otherwise, returns N. SYS_CONTEXT('USERENV', 'SCHEDULER_JOB')

SELECT sys_context('USERENV', 'SCHEDULER_JOB');

      

      SYS_CONTEXT('USERENV','SCHEDULER_JOB')

      --------------------------------------

      N

SERVER_HOST

The host name of the machine on which the instance is running SYS_CONTEXT('USERENV', 'SESSION_USER')

SELECT sys_context('USERENV', 'SERVER_HOST');

      

      SYS_CONTEXT('USERENV','SERVER_HOST')

      ------------------------------------

      perrito5

SERVICE_NAME

The name of the service to which a given session is connected SYS_CONTEXT('USERENV', 'SERVICE_NAME')

-- connect to the CDB

      conn sys@orabase as sysdba

      Enter password: *********

      Connected.

      

      SELECT sys_context('USERENV', 'SERVICE_NAME');

      

      SYS_CONTEXT('USERENV','SERVICE_NAME')

      -------------------------------------------------------------

      orabeta

      

      -- connect to a PDB

      conn uwclass/uwclass@orabase

      Connected.

      

      SELECT sys_context('USERENV', 'SERVICE_NAME');

      

      SYS_CONTEXT('USERENV','SERVICE_NAME')

      -------------------------------------------------------------

      orabase

SESSION_DEFAULT_COLLATION

The default collation for the session, which is set by the ALTER SESSION SET DEFAULT_COLLATION value. SYS_CONTEXT('USERENV', 'SESSION_DEFAULT_COLLATION')

SELECT sys_context('USERENV', 'SESSION_DEFAULT_COLLATION');

      

      SYS_CONTEXT('USERENV','SESSION_DEFAULT_COLLATION')

      --------------------------------------------------

      NONE

SESSION_EDITION_ID

The id number of the current edition in the session SYS_CONTEXT('USERENV', 'SESSION_EDITION_ID')

SELECT sys_context('USERENV', 'SESSION_EDITION_ID');

      

      SYS_CONTEXT('USERENV','SESSION_EDITION_ID')

      -------------------------------------------

      131

SESSION_EDITION_NAME

The name of the current edition in the session SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME')

SELECT sys_context('USERENV', 'SESSION_EDITION_NAME');

      

      SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME')

      ---------------------------------------------

      ORA$BASE

SESSION_USER

Database user name by which the current user is authenticated. Remains the same for the duration of the session. SYS_CONTEXT('USERENV', 'SESSION_USER')

SELECT sys_context('USERENV', 'SESSION_USER');

      

      SYS_CONTEXT('USERENV','SESSION_USER')

      -------------------------------------

      UWCLASS

SESSION_USERID

Identifier of the database user name by which the current user is authenticated SYS_CONTEXT('USERENV', 'SESSION_USERID')

SELECT sys_context('USERENV', 'SESSION_USERID');

      

      SYS_CONTEXT('USERENV','SESSION_USERID')

      ---------------------------------------

                                         
      102

SESSIONID

The auditing session identifier. Cannot be used with distributed SQL statements: Equivalent to the AUDSID column in gv$session. SYS_CONTEXT('USERENV', 'SESSIONID')

SELECT sys_context('USERENV', 'SESSIONID');

      

      SYS_CONTEXT('USERENV','SESSIONID')

      ----------------------------------

                                  
      60074

SID

The session number (different from the session ID) SYS_CONTEXT('USERENV', 'SID')

SELECT sys_context('USERENV', 'SID');

      

      SYS_CONTEXT('USERENV','SID')

      ----------------------------

      10

STATEMENTID

The auditing statement identifier SYS_CONTEXT('USERENV', 'STATEMENTID')

TBD

SYS_SESSION_ROLES

This is a twist on the SYS_CONTEXT function as it does not use USERENV. With this usage SYS_CONTEXT queries the list of the user's current default roles and returns TRUE if the role is granted. SYS_CONTEXT('SYS_SESSION_ROLES', '<role_name>')

conn scott/tiger@pdbdev

      

      SELECT sys_context('SYS_SESSION_ROLES', 'RESOURCE');

      

      SYS_CONTEXT('SYS_SESSION_ROLES','RESOURCE')

      --------------------------------------------

      FALSE

      

      conn sys@pdbdev as sysdba

      

      GRANT resource TO scott;

      

      conn scott/tiger@pdbdev

      

      SELECT sys_context('SYS_SESSION_ROLES', 'RESOURCE');

      

      SYS_CONTEXT('SYS_SESSION_ROLES','RESOURCE')

      --------------------------------------------

      TRUE

TERMINAL

The operating system identifier for the client of the current session SYS_CONTEXT('USERENV', 'TERMINAL')

SELECT sys_context('USERENV', 'TERMINAL');

      

      SYS_CONTEXT('USERENV','TERMINAL')

      ---------------------------------

      PERRITO5

TLS_VERSION

Transport Layer Security version SYS_CONTEXT('USERENV', 'TLS_VERSION')

SELECT sys_context('USERENV', 'TLS_CIPHERSUITE');

      

      SYS_CONTEXT('USERENV','TLS_CIPHERSUITE')

      -----------------------------------------

TLS_VERSION

Returns the version number when connected via Transport Layer Security (TLS) SYS_CONTEXT('USERENV', 'TLS_VERSION')

SELECT sys_context('USERENV', 'TLS_VERSION');

      

      SYS_CONTEXT('USERENV','TLS_VERSION')

      ------------------------------------

      1.3

UNIFIED_AUDIT_SESSIONID

If queried while connected to a database that uses unified auditing or mixed mode auditing, returns the unified audit session ID else returns NULL SYS_CONTEXT('USERENV', 'UNIFIED_AUDIT_SESSIONID')

SELECT sys_context('USERENV', 'UNIFIED_AUDIT_SESSIONID');

      

      SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID')

      -------------------------------------------------

      2134506887

Context Demo

User Created Contexts CREATE OR REPLACE CONTEXT App_Ctx using My_pkg ACCESSED GLOBALLY; CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER IS PROCEDURE set_session_id(p_session_id NUMBER); PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2); PROCEDURE close_session(p_session_id NUMBER); END my_pkg; / CREATE OR REPLACE PACKAGE BODY my_pkg IS g_session_id NUMBER; PROCEDURE set_session_id(p_session_id NUMBER) IS BEGIN g_session_id := p_session_id; dbms_session.set_identifier(p_session_id); END set_session_id; --=============================================== PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS BEGIN dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id); END set_ctx; --=============================================== PROCEDURE close_session(p_session_id NUMBER) IS BEGIN dbms_session.set_identifier(p_session_id); dbms_session.clear_identifier; END close_session; --=============================================== END my_pkg; / col var1 format a10 col var2 format a10 exec my_pkg.set_session_id(1234); exec my_pkg.set_ctx('Var1', 'Val1'); exec my_pkg.set_ctx('Var2', 'Val2'); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2; -- log out and back in ... at first, the context is empty-but once the session is rejoin it appears disconnect connect uwclass/uwclass SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2; exec my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2; -- this context is tied to the specified user above, if NULL was used anyone can join this session). GRANT EXECUTE ON my_pkg TO scott; conn scott/tiger@pdbdev exec uwclass.my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2; -- return to the set context again and clear it conn uwclass/uwclass@pdbdev exec my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2; exec my_pkg.close_session(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2;

Another Demo CREATE TABLE all_objs AS SELECT object_name FROM dba_objects_ae; CREATE VIEW all_objs_view AS SELECT COUNT(*) obj_count FROM all_objs WHERE object_name = sys_context('UW_NAMESPACE', 'UW_PARAMETER'); CREATE OR REPLACE PROCEDURE set_param(valin IN VARCHAR2) AUTHID CURRENT_USER IS BEGIN dbms_session.set_context('UW_NAMESPACE', 'UW_PARAMETER', valin); END; / CREATE CONTEXT uw_namespace USING set_param; exec SET_PARAM(valin => 'DBMS_SQL'); SELECT * FROM all_objs_view; exec set_param(valin => 'ZZZ'); SELECT * FROM all_objs_view;

SYS_CLUSTER_PROPERTIES

CLUSTER_PATCHLVL

Returns a RAC cluster's patch level SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CURRENT_PATCHLVL')

SELECT sys_context('SYS_CLUSTER_PROPERTIES', 'CURRENT_PATCHLVL');

       *

      ERROR at line 2:

      ORA-00439: feature not enabled: Real Application Clusters

CLUSTER_STATE

Determine whether a RAC cluster is in rolling patch mode SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE')

SELECT sys_context('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE');

      *

      ERROR at line 2:

      ORA-00439: feature not enabled: Real Application Clusters