Oracle参数修改浅析

Oracle参数修改浅析SCOPE = SPFILEThe change is applied in the server parameter file only. The effect is as follows:For dy

Oracle参数修改浅析

SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is effective 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.

反过来推理下：

启动和修改方式

动态参数

静态参数

SPFILE

启动

Scope=spfile

更新spfile

更新spfile

重启后永久生效

重启后永久生效

Scope=memory

更新内存

不允许

立即生效，重启后无效

Scope=both(默认)

更新内存和spfile

不允许

立即并永久生效

PFILE

启动

Scope=spfile

不允许

不允许

Scope=memory（默认）

更新内存

不允许

立即生效，，重启后无效

Scope=both

不允许

不允许

从以上可以看出，由于pfile只能手工修改，所以以pfile启动后，仅动态参数可以立即修改生效，但实例重启后无效；以spfile启动，静态参数可以修改重启后生效，动态参数可以以三种方式修改生效。

如何查看一个参数是动态还是静态的，可以通过v$parameter视图中的issys_modifiable获得：

此字段含义：

?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

从解释来看，immediate和deferred都属于动态参数，但deferred比较特殊，对当前已经连接会话不生效。False属于静态参数，只能重启后生效。

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

ISSYS_MOD COUNT(*)

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

IMMEDIATE 145

FALSE 107

DEFERRED 7