Purpose
USERENV() queries information about the current session. This function cannot be used under the CHECK constraint.
Syntax
USERENV('parameter')
Parameters
The following describes the valid values of parameter.
| Value | Description |
|---|---|
| CLIENT_INFO | The information about the user session, which contains up to 64 bytes. Applications can store the information in the DBMS_APPLICATION_INFO system package. |
| INSTANCE | The ID of the current instance. |
| LANG | The abbreviated name of the language, which is shorter than LANGUAGE. |
| LANGUAGE | The language, region, and database character set of the current session. The value is in the language_territory.characterset format. |
| SCHEMAID | The schema ID. |
| SID | SESSIONID | The client session ID of the current session, which is the unique identifier of a session in a client. |
Return value
The return type is NUMBER when the SESSIONID or SID parameter is specified. Otherwise, the return type is VARCHAR2.
Examples
Obtain the language, region, and database character set of the current session.
obclient [SYS]> SELECT USERENV('LANGUAGE') "Language" FROM DUAL;The return result is as follows:
+---------------------------+ | Language | +---------------------------+ | AMERICAN_AMERICA.AL32UTF8 | +---------------------------+ 1 row in setObtain the schema ID of the current session.
obclient [SYS]> SELECT USERENV('SCHEMAID') FROM DUAL;The return result is as follows:
+---------------------+ | USERENV('SCHEMAID') | +---------------------+ | 201006 | +---------------------+ 1 row in setObtain the client session ID of the current session.
obclient [SYS]> SELECT USERENV('SESSIONID') AS Client_Session_ID FROM DUAL;or
obclient [SYS]> SELECT USERENV('SID') AS Client_Session_ID FROM DUAL;The return result is as follows:
+-------------------+ | CLIENT_SESSION_ID | +-------------------+ | 3221488033 | +-------------------+ 1 row in set
References
You can execute the SHOW PROCESSLIST statement to query the quantity and IDs of sessions in the current database. For more information, see View tenant sessions.
