[tips]登录后自动更改会话级别参数

需求是:当需要根据连接到数据库的不同用户设置不同的执行环境是，用一张配置表和一个数据库触发器来实现.
  1 把想要设置的参数以及对应用户设置到配置表中
  2 采用触发器当用户登录的时候更改session级别设置

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
 
SQL> DROP TABLE exec_env_conf PURGE;
 
Table dropped
 
SQL>
SQL> CREATE TABLE exec_env_conf(
  2   username VARCHAR2(30),
  3   parameter VARCHAR2(80),
  4   VALUE VARCHAR2(512)
  5  );
 
Table created
 
SQL>  SELECT name,VALUE FROM V$PARAMETER S WHERE S.NAME='optimizer_mode';
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
optimizer_mode                                                                   ALL_ROWS
 
SQL> insert into exec_env_conf values('SCOTT','optimizer_mode','first_rows_10');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> CREATE or REPLACE TRIGGER execution_environment AFTER logon on DATABASE
  2  BEGIN
  3     FOR c IN (SELECT parameter,VALUE FROM exec_env_conf
  4               WHERE username = sys_context('userenv','session_user'))LOOP
  5        EXECUTE IMMEDIATE 'ALTER SESSION SET '||C.PARAMETER||'='||C.VALUE;
  6 
  7     END LOOP;
  8  END;
  9  /
 
Trigger created
 
SQL>  SELECT name,VALUE FROM V$PARAMETER S WHERE S.NAME='optimizer_mode';
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
optimizer_mode                                                                   ALL_ROWS
 
SQL> conn scott/oracle@test_orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
 
SQL>  SELECT name,VALUE FROM V$PARAMETER S WHERE S.NAME='optimizer_mode';
SQL>
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
optimizer_mode                                                                   FIRST_ROWS_10
 
 
其中 sys_context(‘userenv’，‘namespace’) 函数是oracle内部函数根据不同的namespace可以显示出不同的结果值
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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/184303/viewspace-700749/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/184303/viewspace-700749/