|
这两个函数用来记录连接的session信息经常用于触发器中记录客户端的连接信息（比如IP）我比较关注的是sessionid和ip但是 select userenv(sessionid)得到的并不是用户的sid而是v$session中的audsidsid根据audsid再得到 SQL> select sid from v$session where audsid=userenv(sessionid); SID
至于sid和audsid的区别自己也没太搞明白网上摘了一段 from asktom the audsid column is populated via a sequence and for normal sessions is the same as userenv(sessionid) but for some background sessions it is not set (it comes back as making the view not work So Id just ignore audsid for now and use SID 下面记录了两个函数的具体用法 USERENV(OPTION) 返回当前的会话信息 OPTION=ISDBA若当前是DBA角色则为TRUE否则FALSE OPTION=LANGUAGE返回数据库的字符集 OPTION=SESSIONID为当前会话标识符 OPTION=ENTRYID返回可审计的会话标识符 OPTION=LANG返回会话语言名称的ISO简记 OPTION=INSTANCE返回当前的实例 OPTION=terminal返回当前计算机名 SELECT USERENV(LANGUAGE) FROM DUAL; 返回系统会话信息sys_context select SYS_CONTEXT(USERENVTERMINAL) terminal SYS_CONTEXT(USERENVLANGUAGE) language SYS_CONTEXT(USERENVSESSIONID) sessionid SYS_CONTEXT(USERENVINSTANCE) instance SYS_CONTEXT(USERENVENTRYID) entryid SYS_CONTEXT(USERENVISDBA) isdba SYS_CONTEXT(USERENVNLS_TERRITORY) nls_territory SYS_CONTEXT(USERENVNLS_CURRENCY) nls_currency SYS_CONTEXT(USERENVNLS_CALENDAR) nls_calendar SYS_CONTEXT(USERENVNLS_DATE_FORMAT) nls_date_format SYS_CONTEXT(USERENVNLS_DATE_LANGUAGE) nls_date_language SYS_CONTEXT(USERENVNLS_SORT) nls_sort SYS_CONTEXT(USERENVCURRENT_USER) current_user SYS_CONTEXT(USERENVCURRENT_USERID) current_userid SYS_CONTEXT(USERENVSESSION_USER) session_user SYS_CONTEXT(USERENVSESSION_USERID) session_userid SYS_CONTEXT(USERENVPROXY_USER) proxy_user SYS_CONTEXT(USERENVPROXY_USERID) proxy_userid SYS_CONTEXT(USERENVDB_DOMAIN) db_domain SYS_CONTEXT(USERENVDB_NAME) db_name SYS_CONTEXT(USERENVHOST) host SYS_CONTEXT(USERENVOS_USER) os_user SYS_CONTEXT(USERENVEXTERNAL_NAME) external_name SYS_CONTEXT(USERENVIP_ADDRESS) ip_address SYS_CONTEXT(USERENVNETWORK_PROTOCOL) network_protocol SYS_CONTEXT(USERENVBG_JOB_ID) bg_job_id SYS_CONTEXT(USERENVFG_JOB_ID) fg_job_id SYS_CONTEXT(USERENVAUTHENTICATION_TYPE) authentication_type SYS_CONTEXT(USERENVAUTHENTICATION_DATA) authentication_data from dual ; 其中第二个参数的可选值如下 AUTHENTICATION_DATA Data being used to authenticate the login user For X certificate authenticated sessions this field returns the context of the certificate in HEX format Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax Values of up to are accepted This is the only attribute of USERENV for which Oracle implements such a change AUTHENTICATION_TYPE How the user was authenticated: DATABASE: username/password authentication OS: operating system external user authentication NETWORK: network protocol or ANO authentication PROXY: OCI proxy connection authentication BG_JOB_ID Job ID of the current session if it was established by an Oracle background process Null if the session was not established by a background process CLIENT_INFO Returns up to bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package 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 CURRENT_SCHEMAID Identifier of the default schema being used in the current session CURRENT_USER The name of the user whose privilege the current session is under CURRENT_USERID User ID of the user whose privilege the current session is under DB_DOMAIN Domain of the database as specified in the DB_DOMAIN initialization parameter DB_NAME Name of the database as specified in the DB_NAME initialization parameter ENTRYID The available auditing entry identifier You cannot use this option in distributed SQL statements To use this keyword in USERENV the initialization parameter AUDIT_TRAIL must be set to true EXTERNAL_NAME External name of the database user For SSL authenticated sessions using v certificates this field returns the distinguished name (DN) stored in the user certificate FG_JOB_ID Job ID of the current session if it was established by a client foreground process Null if the session was not established by a foreground process HOST Name of the host machine from which the client has connected INSTANCE The instance identification number of the current instance IP_ADDRESS IP address of the machine from which the client is connected ISDBA TRUE if you are logged on as SYS LANG The ISO abbreviation for the language name a shorter form than the existing LANGUAGE parameter LANGUAGE The language and territory currently used by your session along with the database character set in the form:language_territorycharacterset NETWORK_PROTOCOL Network protocol being used for communication as specified in the PROTOCOL=protocol portion of the connect string NLS_CALENDAR The current calendar of the current session NLS_CURRENCY The currency of the current session NLS_DATE_FORMAT The date format for the session NLS_DATE_LANGUAGE The language used for expressing dates NLS_SORT BINARY or the linguistic sort basis NLS_TERRITORY The territory of the current session OS_USER Operating system username of the client process that initiated the database session PROXY_USER Name of the database user who opened the current session on behalf of SESSION_USER PROXY_USERID Identifier of the database user who opened the current session on behalf of SESSION_USER SESSION_USER Database user name by which the current user is authenticated This value remains the same throughout the duration of the session SESSION_USERID Identifier of the database user name by which the current user is authenticated SESSIONID The auditing session identifier You cannot use this option in distributed SQL statements TERMINAL The operating system identifier for the client of the current session In distributed SQL statements this option returns the identifier for your local session In a distributed environment this is supported only for remote SELECT statements not for remote INSERT UPDATE or DELETE operations (The return length of this parameter may vary by operating system) |
