This is the explanation of this parameter from Oracle. This is the description of the description in the explanation. From the documentation we know that this parameter is not modifiable (Modifiable: No). But in fact I can modify it:

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl';
alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope = spfile;

System altered.


So, what does it mean by Modifiable: No in the documentation?

BTW, this is my Oracle version:

sys@ORCL>select * from v$version;

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE  Production
TNS for Linux: Version - Production
NLSRTL Version - Production



  • I hope you don't mind I 'undeleted' this useful question and answer - if you delete it again, I'll leave it be, but please consider leaving it (and accepting the answer) because I am sure it will be useful to others – Jack says try topanswers.xyz Nov 14 '11 at 11:17

When you see Modifiable you should read MEMORY Modifiable. For ALTER SYSTEM then this would mean can the parameter be modified in memory for the system. The Database Reference document you site includes part of this information:

Modifiable Specifies whether the parameter can be changed for the current session (by an ALTER SESSION statement) or for all sessions in the current instance (by an ALTER SYSTEM statement):

ALTER SYSTEM can be used to change the value in the server parameter file (SPFILE) of any initialization parameter. Such a change takes effect only in subsequent instances. The parameter descriptions indicate only those parameters that can be modified for the current instance.

To understand what will happen in entirety you have to understand the SCOPE parameter and what happens when no SCOPE is specified. From the SQL Language Reference we can see that the ALTER SYSTEM statement can have three different SCOPE specifiers or none at all.

SCOPE The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a traditional plain-text parameter file (pfile) or server parameter file (spfile).

MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.

SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

So, since you were able to specify a scope of SPFILE, we can infer that you are using an SPFILE, so the default for the first ALTER SYSTEM when you did not specify a scope was BOTH which would fail because the parameter is not MEMORY modifiable. You can prove this by repeating your first ALTER SYSTEM and adding SCOPE=BOTH or SCOPE=MEMORY both of which will also fail.


Modifiable means are you allowed to modify it for the SESSION. Read: right now , immediately, while Oracle is still running.

You are modifying it for spfile. This means that changes will be available in the next Restart.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.