Scope of the ALTER SYSTEM command parameter in Oracle

SCOPE

The SCOPE clause lets you specify when the change takes effect. Scope depends on whether your started up the database using a client-side parameter file (pfile) or server parameter file ( SPFile).

Scope can specify when changes to the system take effect, Scope=memory|spfile|both, depending on whether the database uses Pfile or SPFile to start

MEMORY

Memory indicates that's the change was made in memory, takes effect immediately, and persists until the database was shut down . If you started to the database using a parameter file (pfile), then the-the only scope can specify.

Scope=memory, which indicates that the modification takes effect immediately until the database is shut down, which is the only optional and default value if the database is started with pfile.

SPFILE

SPFILE indicates that's the change was made in the server parameter file. The new setting takes effect when the database was next shut down and started up again. Must specify SPFILE when changing the value of a static parameter that's described as not modifiable in Oracle Databa Se Reference.

Scope=spfile, the SPFile parameter is modified, and the new setting takes effect only when the database is restarted using SPFile.

Also, if you modify a static parameter (a parameter that cannot be directly in effect), you must specify Scope=spfile, otherwise the specified memory or both will report the following error:

Ora-02095:specified initialization parameter cannot be modified

Because static parameters cannot be directly modified by modifying the memory, you can only take effect by modifying the SPFile and then restarting the database.

BOTH

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

Scope=both, which indicates that the modification takes effect immediately in memory, and modifies the spfile to ensure that the database restarts also take effect.

If A server parameter file was used to the start of 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 can speci Fy.

If the database starts with SPFile, then Scope=both is the default value

If the database is started with pfile, then scope=memory is the default value and is a unique value.

Static parameters cannot be modified by modifying the memory (default scope value or Scope=memory or Scope=both) to take effect by modifying the SPFile () and restarting the database.

Scope of the ALTER SYSTEM command parameter in Oracle