Analysis on Oracle Parameter Modification

Analysis on Oracle Parameter Modification

SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is valid at the next startup and is persistent.
For static parameters, the behavior. is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:
For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
For static parameters, this specification is not allowed.
SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:
For dynamic parameters, the effect is immediate and persistent.
For static parameters, this specification is not allowed.

 

In reverse reasoning:

Startup and modification methods		Dynamic Parameters	Static Parameters
SPFILE Start	Scope = spfile	Update spfile	Update spfile
		It takes effect permanently after restart	It takes effect permanently after restart
	Scope = memory	Update memory	Not Allowed
		Takes effect immediately. It is invalid after restart.
	Scope = both (default)	Update memory and spfile	Not Allowed
		Effective immediately and permanently
PFILE Start	Scope = spfile	Not Allowed	Not Allowed
	Scope = memory (default)	Update memory	Not Allowed
		Takes effect immediately. It is invalid after restart.
	Scope = both	Not Allowed	Not Allowed

 

As can be seen from the above, since pfile can only be manually modified, after pfile is started, only dynamic parameters can be modified to take effect immediately, but it is not valid after the instance is restarted; start with spfile, static parameters can be modified to take effect after restart, and dynamic parameters can take effect in three ways.

To check whether a parameter is dynamic or static, you can obtain it through issys_modifiable in the v $ parameter View:

Meaning of this field:

? 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

Both immediate and deferred are dynamic parameters, but deferred is special and does not take effect for connected sessions. False is a static parameter and can only take effect after restart.

 

SQL>Select issys_modifiable, count (*) from v $ parameter group by issys_modifiable;

ISSYS_MOD COUNT (*)

-------------------

IMMEDIATE 145

FALSE 107

DEFERRED 7

1 2 Next Page