ORA-02095: specified initialization parameter cannot be modified



Share this article :

You are trying to change a parameter with ALTER SYSTEM option but getting below error,

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Solution

Some initialization parameter cannot be modified using the ALTER SYSTEM command unless if the server parameter (SPFILE) was used during the Oracle database startup. You can refer to Oracle V$PARAMETER documentation on column ISSYS_MODIFIABLE.

ISSYS_MODIFIABLE will indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:

• IMMEDIATE – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

• DEFERRED – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

• FALSE – Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

SQL> select ISSYS_MODIFIABLE from V$PARAMETER where NAME='sga_max_size';

ISSYS_MOD
---------
FALSE

1. Find which file, SPFILE or PFILE used during the Oracle database startup.

2. If the Oracle was using SPFILE, you need to set the command to use SPFILE and restart the Oracle. As example,

ALTER SYSTEM SET SGA_MAX_SIZE=900M; SCOPE=spfile;

3. But, if the Oracle was using PFILE, you may need convert the PFILE to PFILE and do the change and restart the Oracle.

Tags:

Related posts

ORA-01033: ORACLE initialization or shutdown in progress
V$BACKUP_DEVICE: Display Supported Backup Devices
ORA-28001: the password has expired
Grant & Revoke User Privileges in Oracle
© 2017 ITsiti. All Rights Reserved
Powered by KEEM