Oracle参数修改浅析

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 145FALSE 107DEFERRED 7

Oracle all_parameters view 创建sqlOracle 数据大小超出此类型的最大值：2736相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）