会话信息获取函数



1. 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;
2. SYS_CONTEXT
   

   SELECT SYS_CONTEXT('USERENV', 'TERMINAL') TERMINAL,
          SYS_CONTEXT('USERENV', 'LANGUAGE') LANGUAGE,
          SYS_CONTEXT('USERENV', 'SESSIONID') SESSIONID,
          SYS_CONTEXT('USERENV', 'INSTANCE') INSTANCE,
          SYS_CONTEXT('USERENV', 'ENTRYID') ENTRYID,
          SYS_CONTEXT('USERENV', 'ISDBA') ISDBA,
          SYS_CONTEXT('USERENV', 'NLS_TERRITORY') NLS_TERRITORY,
          SYS_CONTEXT('USERENV', 'NLS_CURRENCY') NLS_CURRENCY,
          SYS_CONTEXT('USERENV', 'NLS_CALENDAR') NLS_CALENDAR,
          SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') NLS_DATE_FORMAT,
          SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
          SYS_CONTEXT('USERENV', 'NLS_SORT') NLS_SORT,
          SYS_CONTEXT('USERENV', 'CURRENT_USER') CURRENT_USER,
          SYS_CONTEXT('USERENV', 'CURRENT_USERID') CURRENT_USERID,
          SYS_CONTEXT('USERENV', 'SESSION_USER') SESSION_USER,
          SYS_CONTEXT('USERENV', 'SESSION_USERID') SESSION_USERID,
          SYS_CONTEXT('USERENV', 'PROXY_USER') PROXY_USER,
          SYS_CONTEXT('USERENV', 'PROXY_USERID') PROXY_USERID,
          SYS_CONTEXT('USERENV', 'DB_DOMAIN') DB_DOMAIN,
          SYS_CONTEXT('USERENV', 'DB_NAME') DB_NAME,
          SYS_CONTEXT('USERENV', 'HOST') HOST,
          SYS_CONTEXT('USERENV', 'OS_USER') OS_USER,
          SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') EXTERNAL_NAME,
          SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP_ADDRESS,
          SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') NETWORK_PROTOCOL,
          SYS_CONTEXT('USERENV', 'BG_JOB_ID') BG_JOB_ID,
          SYS_CONTEXT('USERENV', 'FG_JOB_ID') FG_JOB_ID,
          SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') AUTHENTICATION_TYPE,
          SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') AUTHENTICATION_DATA
     FROM DUAL;

   其中，第二个参数的可选值如下：
   
   • AUTHENTICATION_DATA
     
     Data being used to authenticate the login user. For X.503 certificate authenticated sessions, this field returns the context of the certificate in HEX2 format.
     Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax. Values of up to 4000 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 64 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.503 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_territory.characterset.
   • 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.)

转自：http://zhaizhenxing.blog.51cto.com/643480/134745