Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

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.