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;
EXECUTE IMMEDIATE cmmd2;
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.
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
