Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Tuesday, July 19, 2005

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.

13 comments:

Ajay said...

Mike,
Shouldn't user_param's primary key be just (username, parameter_name)? Otherwise, the second value picked up by the cursor will just override the first one.

Ajay said...

Reading my comment again, I realized I wasn't very clear. What I meant to say was - with the existing primary key on (username, parameter_name, value), if there are two records with the same (username, parameter_name), then the second value picked up by the cursor will just override the first one.

Mike said...

Ajay,

Not sure what you mean. It creates multiple settings for each user. You can have more than one parameter setting. It is designed for when multiple parameters are needed for one or more users.

It works.

Mike

Ajay said...

Mike,
Lets say I execute the following two statements:

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

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

With the primary key on username, parameter_name, value), this is a perfectly acceptable situation. What value will APPLTEST's SORT_AREA_SIZE be set to?

Ajay said...

Mike,
Since you're using 'alter session', and have an exception block that will 'eat up' the error, it might be useful to add a lookup to v$parameter.ISSES_MODIFIABLE. Without that, the 'alter session' may fail silently if you try to change user_dump_dest, for example.

Mike said...

In the casre you mention, it would have whichever one it pulled from the table first. You are probably correct. I will correct the primary key.

Like I said at the bottom, this is just a barebones example, more error checking and such need to be added. I agree a call to see if the parameter is session modifiable is a good idea.


Thanks!

Mike

Dave Smith said...

Mike,

you "EXECUTE IMMEDIATE cmmd2;" without ever declaring cmmd2.

Thanks
Dave

Mike said...

You are corect! I edited this example to remove a second line that was there and forgot to remove this call...I'll fix it...

Mike

Tim... said...

The AFTER LOGON triggers are really useful. Like you, I use them for setting the cursor_sharing parameter for 3rd part application connection.

I also find them useful for setting the current_schema parameter. Saves using loads of public or private synonyms.

Cheers

Tim...

Noons said...

Hey Tim: do you know if setting the current schema changes any resource manager allocations? Like, if I logon as user A which is part of resource group X and my logon trigger changes schema to B. Do I now now inherit B's resource group Y, or do I continue using X? Mike, do you know?
Sorry, I don't have a system on hand to test.

Mike said...

Nuno,

I am not sure. If you "become" the schema then I would say yes, but if it is merely acting on their behalf probably not. I would have to experiment to see what happens.

Mike

Noons said...

but you see what the problem is, don't you?
The thing is: we often hear that to avoid excessive use of synonyms/grants, we can use the set curent_schema thing.
Yes we can. But aren't we shooting ourselves on the foot by doing that if we ever need to apply resource-manager?

I'm going to test this in a few days (just as soon as I finish putting out the current fires). Will report here what I find. Thanks for your comment.

Noons said...

OK, got it tested. The drill is:

1) Create the resource groups and assign them to whatever plan you want.

2) Create a user, set its default resource group to whatever you want with
exec DBMS_RESOURCE_MANAGER.
SET_INITIAL_CONSUMER_GROUP
('USER_YOU_WANT','GROUP_YOU_WANT');

3) Add a login trigger to the user to set current_schema=WHATEVER_SCHEMA;

And that's it. The user will login as USER_YOU_WANT, then the default schema will be WHATEVER_SCHEMA (you still have to grant USER_YOU_WANT access to WHATEVER_SCHEMA objects!) and the resource plan associated with GROUP_YOU_WANT will take effect.