Query the User Environment with SYS_CONTEXT

Many times I have granted permissions to various V$ views so I (or developers) could query environment information for a user.  That is mostly unnecessary when using the SYS_CONTEXT function.

Below are a few examples of what the SYS_CONTEXT function can provide.  I’ve done this in a SQL statement format so you can copy and paste the code to run for yourself.


column userenv_name  format a30
column userenv_value format a50
set linesize 85
select  *
from    (
        select 'ACTION' userenv_name, sys_context( 'USERENV', 'ACTION' ) userenv_value from dual
        union all
        select 'CLIENT_IDENTIFIER' userenv_name, sys_context( 'USERENV', 'CLIENT_IDENTIFIER' ) userenv_value from dual
        union all
        select 'CLIENT_INFO' userenv_name, sys_context( 'USERENV', 'CLIENT_INFO' ) userenv_value from dual
        );

exec dbms_application_info.set_action( 'Context TEST Action' );
exec dbms_session.set_identifier( USER || ' ' || SYSTIMESTAMP );
exec dbms_application_info.set_client_info( 'Context TEST Info' );

column userenv_name  format a30
column userenv_value format a50
set linesize 85
select  *
from   (
       select 'ACTION' userenv_name, sys_context( 'USERENV', 'ACTION' ) userenv_value from dual
       union all
       select 'CLIENT_IDENTIFIER' userenv_name, sys_context( 'USERENV', 'CLIENT_IDENTIFIER' ) userenv_value from dual
       union all
       select 'CLIENT_INFO' userenv_name, sys_context( 'USERENV', 'CLIENT_INFO' ) userenv_value from dual
       union all
       select 'CURRENT_SCHEMA' userenv_name, sys_context( 'USERENV', 'CURRENT_SCHEMA' ) userenv_value from dual
       union all
       select 'CURRENT_SCHEMAID' userenv_name, sys_context( 'USERENV', 'CURRENT_SCHEMAID' ) userenv_value from dual
       union all
       select 'DB_DOMAIN' userenv_name, sys_context( 'USERENV', 'DB_DOMAIN' ) userenv_value from dual
       union all
       select 'DB_NAME' userenv_name, sys_context( 'USERENV', 'DB_NAME' ) userenv_value from dual
       union all
       select 'DB_UNIQUE_NAME' userenv_name, sys_context( 'USERENV', 'DB_UNIQUE_NAME' ) userenv_value from dual
       union all
       select 'GLOBAL_CONTEXT_MEMORY' userenv_name, sys_context( 'USERENV', 'GLOBAL_CONTEXT_MEMORY' ) userenv_value from dual
       union all
       select 'HOST' userenv_name, sys_context( 'USERENV', 'HOST' ) userenv_value from dual
       union all
       select 'IDENTIFICATION_TYPE' userenv_name, sys_context( 'USERENV', 'IDENTIFICATION_TYPE' ) userenv_value from dual
       union all
       select 'INSTANCE' userenv_name, sys_context( 'USERENV', 'INSTANCE' ) userenv_value from dual
       union all
       select 'INSTANCE_NAME' userenv_name, sys_context( 'USERENV', 'INSTANCE_NAME' ) userenv_value from dual
       union all
       select 'ISDBA' userenv_name, sys_context( 'USERENV', 'ISDBA' ) userenv_value from dual
       union all
       select 'LANG' userenv_name, sys_context( 'USERENV', 'LANG' ) userenv_value from dual
       union all
       select 'LANGUAGE' userenv_name, sys_context( 'USERENV', 'LANGUAGE' ) userenv_value from dual
       union all
       select 'MODULE' userenv_name, sys_context( 'USERENV', 'MODULE' ) userenv_value from dual
       union all
       select 'NLS_CALENDAR' userenv_name, sys_context( 'USERENV', 'NLS_CALENDAR' ) userenv_value from dual
       union all
       select 'NLS_CURRENCY' userenv_name, sys_context( 'USERENV', 'NLS_CURRENCY' ) userenv_value from dual
       union all
       select 'NLS_DATE_FORMAT' userenv_name, sys_context( 'USERENV', 'NLS_DATE_FORMAT' ) userenv_value from dual
       union all
       select 'NLS_DATE_LANGUAGE' userenv_name, sys_context( 'USERENV', 'NLS_DATE_LANGUAGE' ) userenv_value from dual
       union all
       select 'NLS_SORT' userenv_name, sys_context( 'USERENV', 'NLS_SORT' ) userenv_value from dual
       union all
       select 'NLS_TERRITORY' userenv_name, sys_context( 'USERENV', 'NLS_TERRITORY' ) userenv_value from dual
       union all
       select 'OS_USER' userenv_name, sys_context( 'USERENV', 'OS_USER' ) userenv_value from dual
       union all
       select 'SERVER_HOST' userenv_name, sys_context( 'USERENV', 'SERVER_HOST' ) userenv_value from dual
       union all
       select 'SERVICE_NAME' userenv_name, sys_context( 'USERENV', 'SERVICE_NAME' ) userenv_value from dual
       union all
       select 'SESSION_USER' userenv_name, sys_context( 'USERENV', 'SESSION_USER' ) userenv_value from dual
       union all
       select 'SESSION_USERID' userenv_name, sys_context( 'USERENV', 'SESSION_USERID' ) userenv_value from dual
       union all
       select 'SESSIONID' userenv_name, sys_context( 'USERENV', 'SESSIONID' ) userenv_value from dual
       union all
       select 'SID' userenv_name, sys_context( 'USERENV', 'SID' ) userenv_value from dual
       union all
       select 'STATEMENTID' userenv_name, sys_context( 'USERENV', 'STATEMENTID' ) userenv_value from dual
       union all
       select 'TERMINAL' userenv_name, sys_context( 'USERENV', 'TERMINAL' ) userenv_value from dual
       );

Below is sample output from the query.

USERENV_NAME                   USERENV_VALUE
------------------------------ --------------------------------------------------
ACTION                         Context TEST Action
CLIENT_IDENTIFIER              NOVAPRD 09-NOV-10 22.07.32.384737000 AM -08:00
CLIENT_INFO                    Context TEST Info
CURRENT_SCHEMA                 MICHAEL
CURRENT_SCHEMAID               36
DB_DOMAIN                      my.domain
DB_NAME                        fmd
DB_UNIQUE_NAME                 fmd
GLOBAL_CONTEXT_MEMORY          0
HOST                           DOMAIN\MICHAELDESK
IDENTIFICATION_TYPE            LOCAL
INSTANCE                       1
INSTANCE_NAME                  fmd
ISDBA                          FALSE
LANG                           US
LANGUAGE                       AMERICAN_AMERICA.WE8ISO8859P1
MODULE                         SQL*Plus
NLS_CALENDAR                   GREGORIAN
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
OS_USER                        michael
SERVER_HOST                    dbhome01
SERVICE_NAME                   fmd.my.domain
SESSIONID                      65418102
SESSION_USER                   MICHAEL
SESSION_USERID                 36
SID                            1525
STATEMENTID
TERMINAL                       MICHAELDESK

Post a Comment