userenv和sys

　　這兩個函數用來記錄連接的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)

From:http://tw.wingwit.com/Article/program/Oracle/201311/16831.html