alter system set ...scope.... 杂谈

【以下知识，困惑好久的说。。。Mark一下】

从Oracle9i开始，ALTER SYSTEM命令增加了一个新的选项scope。scope参数有3个可选值：memory、spfile和both。

       ●memory：只改变当前实例运行，重新启动数据库后失效。
       ●spfile    ：只改变spfile的设置，不改变当前实例运行，重新启动数据库后生效。
       ●both     ：同时改变实例及spfile，当前更改立即生效，重新启动数据库后仍然有效。
        可以通过ALTER SYSTEM或者导入导出来更改spfile的内容。
       针对RAC环境，ALTER SYSTEM还可以指定SID参数，对不同实例进行不同设置。
       通过spfile修改参数的完整命令如下：
       alter system set <parameter_name>=<value> scope=memory|spfile|both [sid=<sid_name>]
from offical doc:官方的文档的确是个好东西啊，仔细研读啊，什么都有....
===================================
SCOPE Clause Description
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.


It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a text initialization parameter file was used to start up the instance.
For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.
The following statement changes the maximum number of job queue processes allowed for the instance. It includes a comment, and explicitly states that the change is to be made only in memory (that is, it is not persistent across instance shutdown and startup).
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50 COMMENT='temporary change on Nov 29' SCOPE=MEMORY;