VERIFY IF PARAMETER IS STATIC OR DYNAMIC
These columns determine the control of initialization parameters that can be modified at session or system and static or dynamic -> isses_modifiable, issys_modifiable
kIsH@STAX<>SELECT name,isses_modifiable,issys_modifiable from v$parameter where name like '%sga_max%';
NAME ISSES ISSYS_MOD
-------------------------------------------------------------------------------- ----- ---------
sga_max_size FALSE FALSE
kIsH@STAX<>SELECT name,isses_modifiable,issys_modifiable from v$parameter where name like '%sga_target%';
NAME ISSES ISSYS_MOD
-------------------------------------------------------------------------------- ----- ---------
sga_target FALSE IMMEDIATE
Example of parameter modification scope.
kIsH@STAX<>ALTER SYSTEM SET sga_max_size=3000M; --scope=spfile
ALTER SYSTEM SET sga_max_size=3000M
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
kIsH@STAX<>ALTER SYSTEM SET sga_target=2304M; --scope=memory or spfile or both
System altered.
kIsH@STAX<>ALTER SESSION SET sga_target=2304M; --Not modifiable at session level
ALTER SESSION SET sga_target=2304M
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option