Check whether a parameter is modifiable at system level or session level
Posted by decipherinfosys on February 14, 2009
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.
(CASE WHEN ISSES_MODIFIABLE=’TRUE’ THEN ‘Yes’
END) AS “Session Modifiable”,
(CASE WHEN ISSYS_MODIFIABLE=’IMMEDIATE’ THEN ‘With Immediate Effect’
WHEN ISSYS_MODIFIABLE=’DEFERRED’ THEN ‘New Sessions’
END) AS “System Modifiable”,
(CASE WHEN ISINSTANCE_MODIFIABLE=’TRUE’ THEN ‘Yes’
END) AS “Instance Modifiable”
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.