Monday, January 5, 2015

Changing session settings automatically for a user when they login

We needed to set some parameters for specific users when they logged in since the client didn't want them changed at database level.

This was achieved by using login triggers to run commands when the specific user logged in.

Hopefully this won't need much explanation:

create or replace TRIGGER set_params_at_login
after logon on database

DECLARE
  lv_username VARCHAR2(100) := NULL;
begin
  lv_username := UPPER(SUBSTR(SYS_CONTEXT('USERENV', 'SESSION_USER'),
                              1,
                              100));

   /* Oracle Parameters for USER1 to reduce CPU */

   /* USER1 */
    if NVL(lv_username, 'X') IN ('USER1' ) then
      execute immediate 'alter session set optimizer_features_enable=''10.2.0.5''';
      execute immediate 'alter session set "_unnest_subquery"=true';
      execute immediate 'alter session set query_rewrite_enabled=false';
      execute immediate 'alter session set optimizer_index_cost_adj=100';
      execute immediate 'alter session set optimizer_index_caching=50';
    end if;

  exception
  when others then
    null;
end;

No comments:

Post a Comment