Tailoring Parameters in Oracle By User

Many times I am asked how to tailor the initialization parameters for a specific process or user. Essentially the easiest way to set custom initialization parameters for a particular session is to use the ALTER SESSION SET command in a logon trigger keyed to a specific schema or schemas. For example, say we had an application where we wanted to setup cursor sharing and all of the users who will need it are prefixed with “APPL” the logon trigger would resemble:

CREATE OR REPLACE TRIGGER set_cursor_sharing AFTER LOGON ON DATABASE
DECLARE
username VARCHAR2(30);
cmmd VARCHAR2(64);
BEGIN
cmmd:='ALTER SESSION SET CURSOR_SHARING=SIMILAR';
username:=SYS_CONTEXT('USERENV','SESSION_USER');
IF username LIKE 'APPL%' then
EXECUTE IMMEDIATE cmmd;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
However, this is a very simplistic trigger and should only be used as a template. If you are in an environment where the usernames aren’t quite so restricted you could either use and IN list, or, better yet and offering the most flexibility, a small table, which could contain both the username and the parameter as well as the required setting. For example:

SQL> desc user_param
Name Null? Type
----------------------------------------- -------- ------------
USERNAME NOT NULL VARCHAR2(64)
PARAMETER_NAME NOT NULL VARCHAR2(64)
SETTING NOT NULL VARCHAR2(64)

Also make a primary key across the first two columns to make sure no duplicates get entered:
(Thanks Ajay for catching this)

SQL> alter table user_param add constraint pk_user_param primary key (username, parameter_name);

Then the trigger becomes:

CREATE OR REPLACE TRIGGER set_cursor_sharing AFTER
LOGON ON DATABASE
DECLARE
usern VARCHAR2(30);
username varchar2(30);
cmmd VARCHAR2(64);
i integer;
cursor get_values(usern varchar2) IS
SELECT * FROM user_param where username=usern;
type usernt is table of user_param%ROWTYPE;
user_param_t usernt;
BEGIN
username:=SYS_CONTEXT('USERENV','SESSION_USER');
FOR user_param_t in get_values(username)
LOOP
IF substr(user_param_t.parameter_name,1,1)!='_'
THEN
SELECT
'ALTER SESSION SET '
user_param_t.parameter_name'='
user_param_t.setting INTO cmmd FROM dual;
ELSE
SELECT
'ALTER SESSION SET 'chr(34)
user_param_t.parameter_namechr(34)'='
user_param_t.setting INTO cmmd FROM dual;
END IF;
-- dbms_output.put_line(cmmd);
EXECUTE IMMEDIATE cmmd;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
-- dbms_output.put_line('err on: 'cmmd);
END;
/
Now we can simply add a row to our user_param table and next time the user logs in they will get the new setting. This new form also allows us to have different settings for different schemas. We may want to make a specific users sort area larger, or smaller, another we may want to set specific hash or other settings. Any settings added must be session alterable.

For example:

SQL> insert into user_param values (‘APPLTEST’,’SORT_AREA_SIZE’,’10M’);

Will reset the user APPLTEST’s sort_area_size to 10m on next login.

The procedure will also handle any undocumented parameters that begin with an underscore, or event settings.

The above trigger should be modified to include more robust exception handling and is for example purposes only.

And now...for a commercial message...

Burleson Consulting job openings for 5-10 certified Oracle DBA’s with HTML-DB experience. It’s a long-term contract, 100% work-at-home, developing an exciting online system with HTML-DB. If you meet the minimum requirements, just click here to submit your Curriculum Vitae or Resume for immediate consideration.