Archives February 9, 2023

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