Oracle | SYS

Oracle SYS_CONTEXT Version 20c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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>) FROM dual; 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') FROM dual;

      

      exec dbms_application_info.set_action('INSERTING');

      

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

APPLICATION_NAME

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

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

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') FROM dual;

AUTHENTICATED_IDENTITY

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

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

      

      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') FROM dual;

AUTHENTICATION_METHOD

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

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

      

      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') FROM dual;

CDB_NAME

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

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

      

      SYS_CONTEXT('USERENV','CDB_NAME')

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

      orabeta

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') FROM dual;

      

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

      

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

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') FROM dual;

      

      exec dbms_application_info.set_client_info('TEST');

      

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

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') FROM dual;

      

      SYS_CONTEXT('USERENV','CLIENT_PROGRAM_NAME')

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

      sqlplus.exe

CON_ID

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

conn / as sysdba

      

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

      

      SYS_CONTEXT('USERENV','CON_ID')

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

      1

CON_NAME

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

conn / as sysdba

      

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

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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 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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

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') FROM dual;

      

      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')

      FROM dual;

      

      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

      

      SELECT sys_context('USERENV', 'DB_SUPPLEMENTAL_LOG_LEVEL')

      FROM dual;

      

      SYS_CONTEXT('USERENV','DB_SUPPLEMENTAL_LOG_LEVEL')

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

      MINIMAL

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') FROM dual;

      

      SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')

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

      test21db

      

      SELECT name, value

      FROM  v$parameter

      where name LIKE 'db%name';

DBLINK_INFO

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

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

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') FROM dual;

ENTERPRISE_IDENTITY

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

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

EXTERNAL_NAME

Returns the workstation and workstation user name SYS_CONTEXT('USERENV', 'EXTERNAL_NAME')

conn uwclass/uwclass@pdbdev

      

      SELECT sys_context('USERENV', 'EXTERNAL_NAME') 
      FROM dual;

      

      SYS_CONTEXT('USERENV','EXTERNAL_NAME')

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

      PERRITO5\oracle

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') FROM dual;

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') FROM dual;

      

      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') FROM dual;

HOST

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

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

      

      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') FROM dual;

      

      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') FROM dual;

INSTANCE_NAME

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

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

IP_ADDRESS

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

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

      

      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')

      FROM dual;

      

      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')

      FROM dual;

      

      SYS_CONTEXT('USERENV','IS_DG_ROLLING_UPGRADE')

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

      FALSE

ISDBA

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

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

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

MODULE

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

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

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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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')

      FROM dual;

      

      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') FROM dual;

      

      SYS_CONTEXT('USERENV','OS_USER')

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

      perrito5\oracle

PID (new 20c)

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') FROM dual;

      

      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')

      FROM dual;

      

      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') FROM dual;

PROXY_ENTERPRISE_IDENTITY

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

TBD

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') FROM dual;

      

      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') FROM dual;

      

      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')

      FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      SYS_CONTEXT('USERENV','SERVICE_NAME')

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

      orabeta

      

      -- connect to a PDB

      conn uwclass/uwclass@orabase

      Connected.

      

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

      

      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. To run this the database must be configured for 12.2 or higher with MAX_STRING_SIZE = EXTENDED. SYS_CONTEXT('USERENV', 'SESSION_DEFAULT_COLLATION')

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

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      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') FROM dual;

      

      SYS_CONTEXT('USERENV','SESSIONID')

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

                                  
      60074

SID

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

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

      

      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', 'SUPERVISOR')

conn scott/tiger@pdbdev

      

      SELECT sys_context('SYS_SESSION_ROLES', 'RESOURCE')

      FROM dual;

      

      SYS_CONTEXT('SYS_SESSION_ROLES','SUPERVISOR')

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

      FALSE

      

      conn sys@pdbdev as sysdba

      

      GRANT resource TO scott;

      

      conn scott/tiger@pdbdev

      

      SELECT sys_context('SYS_SESSION_ROLES', 'RESOURCE')

      FROM dual;

      

      SYS_CONTEXT('SYS_SESSION_ROLES','SUPERVISOR')

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

      TRUE

TERMINAL

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

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

      

      SYS_CONTEXT('USERENV','TERMINAL')

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

      PERRITO5

TLS_VERSION (new 21c)

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

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

      

      SYS_CONTEXT('USERENV','TLS_VERSION')

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

      1.2

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') FROM dual;

      

      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 FROM dual; -- 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 FROM dual; exec my_pkg.set_session_id(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual; -- 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 FROM dual; -- 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 FROM dual; exec my_pkg.close_session(1234); SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2 FROM dual;

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')

      FROM dual;

       *

      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')

      FROM dual;

      *

      ERROR at line 2:

      ORA-00439: feature not enabled: Real Application Clusters