Parameter used with USERENV in Oracle to get current session value.

List of Parameter used with USERENV to get the current value for session in Oracle.

LANGUAGE: Return the language and territory currently used by your session.

SQL> SELECT SYS_CONTEXT('USERENV','LANGUAGE') "Language and Territory settings" from dual;

Language and Territory settings
--------------------------------
AMERICAN_AMERICA.AL32UTF8

LANG: return short name of language name.

SQL> SELECT SYS_CONTEXT('USERENV','LANG') "Language settings" from dual;

Language settings
------------------
US

SESSIONID: return auditing session identifier. You can use this with audsid column of V$session view to identify the session.
Example : SELECT program FROM v$session WHERE audsid= sys_context(‘USERENV’,’SESSIONID’);

SQL> SELECT SYS_CONTEXT('USERENV','SESSIONID') "SESSIONID" from dual;

SESSIONID
---------------
700033

ISDBA: return TRUE if you currently user have the DBA permission else FALSE.

SQL> SELECT SYS_CONTEXT('USERENV','ISDBA') "ISDBA" from dual;

ISDBA
-------
FALSE

Following are the more list of parameters used with USERENV for getting more information:

CURRENT_USER: return the current user of the session.
INSTANCE: Return instance identification number of the current instance.
TERMINAL: The operating system identifier for the client of the current session.
CLIENT_INFO: returns user session information stored with help of DBMS_APPLICATION_INFO package.
CURRENT_USERID: return the current User ID of the session.
SESSION_USER: return the Database user name by which the current user is authenticated.
SESSION_USERID: return the id of database user.
CURRENT_SCHEMA: return the schema name being used as current schema.
you can change with ALTER SESSION SET CURRENT_SCHEMA = ‘HR’ statement.
CURRENT_SCHEMAID: return the default schema id for the current session.
PROXY_USER: return database user name which used the current session on behalf of SESSION_USER.
PROXY_USERID: return database user id which used the current session on behalf of SESSION_USER.
DB_DOMAIN: return domain of the database like DB_DOMAIN parameter.
DB_NAME: return name of the database like DB_NAME parameter.
HOST: return name of the host machine from which the client has connected.
OS_USER: return Operating system username of the client process.
IP_ADDRESS: return IP address of the machine from which the client is connected.
NETWORK_PROTOCOL: return Network protocol number used for communication like ‘PROTOCOL=tcp’ in connect string.
ENTRYID : auditing entry identifier if audit is enabled.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.