Systems Engineering and RDBMS

Check whether a parameter is modifiable at system level or session level

Posted by decipherinfosys on February 14, 2009

In one of our previous blog post, we showed how we can check database parameters and their values in different ways in Oracle and in SQLServer and DB2 LUW.

Just like in other RDBMS, in Oracle also the parameters can be changed at the session level such that the change in parameter effects only current session where the change is made or at the system level, which effects all the sessions across the database.  Not all the parameters are changeable at session level or even at system level. At system level some parameter change takes effect immediately for all sessions, some parameter changes are deferred, means it is only applied to new or future sessions after change is made. All current sessions retain the old values. Some parameters are not modifiable unless server parameter file is used to start the instance. Changing such parameter values require bouncing of the instance.

Using the following SQL, we can see which parameters are modifiable at the system level and which ones are modifiable at the session level.

SELECT name,
(CASE WHEN ISSES_MODIFIABLE=’TRUE’ THEN ‘Yes’
ELSE ‘No’
END) AS “Session Modifiable”,
(CASE WHEN ISSYS_MODIFIABLE=’IMMEDIATE’ THEN ‘With Immediate Effect’
WHEN ISSYS_MODIFIABLE=’DEFERRED’ THEN ‘New Sessions’
ELSE ‘No’
END) AS “System Modifiable”,
(CASE WHEN ISINSTANCE_MODIFIABLE=’TRUE’ THEN ‘Yes’
ELSE ‘No’
END) AS “Instance Modifiable”
FROM v$parameter
ORDER BY name

When issys_modifiable value is set as ‘DEFERRED’ changing such parameters requires use of following clause.

SQL>ALTER SYSTEM SET <parameter> DEFERRED;

IF ‘DEFERRED’ is not used, Oracle will give an error. When value is ‘IMMEDIATE’, use of ‘DEFERRED’ clause is optional.

There is also isinstnace_modifiable column, which is related to RAC.  If this value is set to ‘TRUE’ for a parameter, it means that there can be different value of the same parameter in different instance in RAC.  If the value is set to ‘FALSE’, it means that particular parameter should always have the same value in all RAC instances. If issys_modifiable value is ‘FALSE’ for the specific parameter then isinstance_modifiable is always ‘FALSE’ for the same parameter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: