One may find out if a parameter can be changed in a session or system by querying V$parameter.
If isses modifiable is TRUE, then the value may be changed during the session; if it is FALSE, then ALTER SESSION SET cannot be used to change it.
If the value of issys modifiable is DEFERRED, it can be modified by specifying scope=spfile (if spfile is used), and the change will be reflected in sessions that follow.
If issys modifiable = FALSE, only spfile may be used to alter the value using ALTER SYSTEM, and the change won't take effect until the instance is restarted.
With scope=[memory|both|spfile], the value can be modified if issys modifiable = IMMEDIATE.
One may find out if a parameter can be changed in a session or system by querying V$parameter.
If isses modifiable is TRUE, then the value may be changed during the session; if it is FALSE, then ALTER SESSION SET cannot be used to change it.
If the value of issys modifiable is DEFERRED, it can be modified by specifying scope=spfile (if spfile is used), and the change will be reflected in sessions that follow.
If issys modifiable = FALSE, only spfile may be used to alter the value using ALTER SYSTEM, and the change won't take effect until the instance is restarted.
With scope=[memory|both|spfile], the value can be modified if issys modifiable = IMMEDIATE.
select name, ISSYS_MODIFIABLE from v$parameterISSYS_MODIFIABLE = FALSE : instance restart needed
ISSYS_MODIFIABLE = DEFERRED : session(s) has to be reconnected to "see" new value - no need to retart instance
ISSYS_MODIFIABLE= IMMEDIATE : no need to restart instance