How Find User Information (Oracle)

In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function insteadUSERENV is an Oracle provided namespace that describes the current session, We can find connect user information easly:

Syntax: SELECT sys_context('', '', );FROM dual;

Example: Find User information (host,terminal,IP,Operating User)

SELECT SYS_CONTEXT('USERENV','HOST') , SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','IP_ADDRESS'), SYS_CONTEXT('USERENV','OS_USER')FROM DUAL

ACTION

Identifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI.

SELECT sys_context('USERENV', 'ACTION') FROM dual;exec dbms_application_info.set_action('INSERTING');

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

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 user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.
SELECT sys_context('USERENV', 'CLIENT_INFO') FROM dual;exec dbms_application_info.set_client_info('TEST');SELECT sys_context('USERENV', 'CLIENT_INFO') FROM dual;

CURRENT_BIND
The bind variables for fine-grained auditing
CURRENT_EDITION_ID
The numeric identifier of the current edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;

CURRENT_EDITION_NAME
The name of the current edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

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.
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

CURRENT_SCHEMAID
Identifier of the default schema being used in the current session.
SELECT sys_context('USERENV', 'CURRENT_SCHEMAID') FROM dual;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 event.
CURRENT_SQLn
CURRENT_SQLn attributes return subsequent 4K-byte increments, where n can be an integer from 1 to 7, inclusive. CURRENT_SQL1 returns bytes 4K to 8K; CURRENT_SQL2 returns bytes 8K to 12K, and so forth. You can specify these attributes only inside the event handler for the fine-grained auditing feature.

CURRENT_SQL_LENGTH
The length of the current SQL statement that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers. Valid only inside the function or event handler.

DB_DOMAIN
Domain of the database as specified in the DB_DOMAIN initialization parameter.
SELECT sys_context('USERENV', 'DB_DOMAIN') FROM dual;

DB_NAME
Name of the database as specified in the DB_NAME initialization parameter.
SELECT sys_context('USERENV', 'DB_NAME') FROM dual;SELECT name, valueFROM gv$parameterwhere name LIKE 'db%name';

DB_UNIQUE NAME
Name of the database as specified in the DB_UNIQUE_NAME initialization parameter.
SELECT sys_context('USERENV', 'DB_UNIQUE_NAME') FROM dual;SELECT name, valueFROM gv$parameterwhere name LIKE 'db%name'

INSTANCE
The instance identification number of the current instance.
SELECT sys_context('USERENV', 'INSTANCE') FROM dual;

INSTANCE_NAME
The name of the instance.
SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual;

IP_ADDRESS
IP address of the machine from which the client is connected.

ISDBA
TRUE if the session is SYS
SELECT sys_context('USERENV', 'ISDBA') FROM dual;

LANG
The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
SELECT sys_context('USERENV', 'LANG') FROM dual;

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

MODULE
The application name (module) set through the DBMS_APPLICATION_INFO package or OCI.
SELECT sys_context('USERENV', 'MODULE') FROM dual;

NLS_CALENDAR
The current calendar of the current session.
SELECT sys_context('USERENV', 'NLS_CALENDAR') FROM dual;

NLS_CURRENCY
The currency of the current session.
SELECT sys_context('USERENV', 'NLS_CURRENCY') FROM dual;

NLS_DATE_FORMAT
The date format for the session.
SELECT sys_context('USERENV', 'NLS_DATE_FORMAT') FROM dual;

NLS_DATE_LANGUAGE
The language used for expressing dates.
SELECT sys_context('USERENV', 'NLS_DATE_LANGUAGE') FROM dual;

NLS_SORT
BINARY or the linguistic sort basis.
SELECT sys_context('USERENV', 'NLS_SORT') FROM dual;

NLS_TERRITORY
The territory of the current session.
SELECT sys_context('USERENV', 'NLS_TERRITORY') FROM dual;

OS_USER
Operating system username of the client process that initiated the database session.
SELECT sys_context('USERENV', 'OS_USER') FROM dual;