软件开发培训班 >> 编程开发 >> Oracle


Oracle中获取会话信息的两个函数分享

　　本文标签：Oracle,获取会话信息,函数

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

3.与系统视图v$session组合使用可以获得更多信息(客户端所使用的应用程序等)
复制代码 代码如下:

select *
  from v$session se,
       (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) base
 where se.AUDSID = base.sessionid;