 |
Blogs
Toad and Database Commentaries |
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
Location: Blogs
Mike Ault's Blog
|
|
| MikeA |
Tuesday, December 26, 2006 5:03 PM |
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 pin_procedures AFTER STARTUP ON DATABASE
DECLARE
cmmd VARCHAR2(1000);
Objectowner sys.pin_objects.object_owner%type;
objectname sys.pin_objects.object_name%type;
objecttype VARCHAR2(1);
CURSOR get_objects IS
SELECT object_owner, object_name,
decode( object_type,
'PROCEDURE','P',
'TRIGGER','R',
'SEQUENCE','Q','P')
FROM SYS.PIN_OBJECTS;
BEGIN
OPEN GET_OBJECTS;
FETCH GET_OBJECTS INTO objectowner,objectname,objecttype;
LOOP
EXIT WHEN GET_OBJECTS%NOTFOUND;
cmmd:='EXECUTE
DBMS_SHARED_POOL.KEEP('||chr(39)||objectowner||'.'||objectname||chr(39)||','||objecttype||chr(39)||')';
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 USERNAME and PARAMETER_NAME columns to make sure no duplicates get entered:
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_name||chr(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.
|
| Copyright ©2006 Quest Software Inc. |
| Permalink |
Trackback |
|
|
|
|
|
|
|
|
|