Oracle数据库 USERENV和SYS_CONTEXT

userenv

Note：USERENV is a legacy function that is retained for backward compatibility. Oracle recommends that you use the SYS_CONTEXT function with the built-in USERENV namespace for current functionality. See SYS_CONTEXT for more information.

USERENV returns information about the current session. This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session. You cannot use USERENV in the condition of a CHECK constraint. Table 7-12 describes the values for the parameter argument.

Table 7-12 Parameters of the USERENV Function

Parameter	Return Value
CLIENT_INFO	CLIENT_INFO returns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package. Caution: Some commercial applications may be using this context value. Refer to the applicable documentation for those applications to determine what restrictions they may impose on use of this context area. See Also: Oracle Database Security Guide for more information on application context, CREATE CONTEXT, and SYS_CONTEXT
ENTRYID	The current audit entry number. The audit entryid sequence is shared between fine-grained audit records and regular audit records. You cannot use this attribute in distributed SQL statements.
ISDBA	ISDBA returns ‘TRUE’ if the user has been authenticated as having DBA privileges either through the operating system or through a password file.
LANG	LANG returns the ISO abbreviation for the language name, a shorter form than the existing ‘LANGUAGE’ parameter.
LANGUAGE	LANGUAGE returns the language and territory used by the current session along with the database character set in this form:language_territory.characterset
SESSIONID	SESSIONID returns the auditing session identifier. You cannot specify this parameter in distributed SQL statements.
SID	SID returns the session ID.
TERMINAL	TERMINAL returns the operating system identifier for the terminal of the current session. In distributed SQL statements, this parameter returns the identifier for your local session. In a distributed environment, this parameter is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations.

参数	功能
CLINET_INFO	返回最高可达64个字节存储的用户会话信息,可由应用程序使用DBMS_APPLICATION_INFO包。
ENTRYID	返回当前审计条目编号。审计的EntryID序列细粒度的审计记录和定期审计记录之间共享。在分布式SQL语句不能使用这个属性。
ISDBA	如果用户已经被认证为dba;或者是通过操作系统或口令文件具有DBA特权的，返回“TRUE"，否则返回"FALSE"
LANG	返回ISO缩写语言名称，一个比现有的“语言”参数较短的形式
LNAGUAGE	返回数据库当前会话的语言、地域和字符集
SESSIONID	返回审计会话标识符。在分布式SQL语句不能指定此参数
SID	返回数据库会话ID
TERMINAL	返回当前会话的终端操作系统的标识符。在分布式SQL语句，此参数返回了标识符为本地会话。在分布式环境中，此参数只支持远程SELECT语句，不用于远程INSERT，UPDATE或DELETE操作。

案例

-- ISO缩写语音名称
SQL> select userenv('lang') from dual;

USERENV('LANG')
----------------------------------------------------
US


-- 查看Oracle服务端字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8


-- 查看连接终端类型
SQL> select userenv('TERMINAL') from dual;
USERENV('TERMINAL')
------------------------------------------------------------
pts/2

-- 审计会话标识符
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
          4294967295

-- 查询会话ID
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
          1520

-- 想查看自己是不是dba权限用户
SQL> show user
USER is "SYS"
SQL> select userenv('isdba') from dual;

USEREN
------
TRUE

sys_context

sys_context函数是Oracle提供的一个获取环境上下文信息的预定义函数。该函数用来返回一个指定namespace下的parameter值。

该函数可以在sql和PL/SQL语言中使用。

sys_context实际上就是一个Oracle存储和传递参数的容器访问函数。我们登入Oracle服务器，是带有会话信息和其他一些属性信息的。其中，有一些是Oracle预定义的，登录系统的时候自动填入到指定的变量中。另一个函数userenv()保留与之兼容。

-- 以下是可以通过sys_context函数获取到的一些信息：
select sys_context('USERENV', 'TERMINAL') terminal,
       sys_context('USERENV', 'LANGUAGE') language,
       sys_context