alter system set | reset及参数解释

1. alter system set processes=200 scope=(both | spfile | memory)  sid=(sid | *)

1.1 scope参数

1.2 sid参数

1.3 模拟

初始值

SQL> show parameter processes

NAME            TYPE        VALUE
-----------      --------    --------------
processes       integer    200

【FALSE】静态参数修改

SQL> alter system set processes=150 scope=memory;
alter system set processes=150 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set processes=150 scope=both;
alter system set processes=150 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set processes=150 scope=spfile;

System altered.

重启数据库生效

SQL> show parameter processes;

NAME                TYPE              VALUE
-----------          -------------    ------------
processes          integer            150

由于此参数为静态参数，所以内存修改报错

在oracle中通过视图v$parameter的issys_modifiable 列的值可以判断

SQL> select name,issys_modifiable from v$parameter;

NAME                                  ISSYS_MOD
-------------------                ------------------------------------------------------------- ---------

processes                            FALSE
open_cursors                      IMMEDIATE
backup_tape_io_slaves       DEFERRED

关于scope范围和issys_modifiable参数类型比较

【IMMEDIATE】动态参数修改

SQL> show parameter open_cursors

NAME                    TYPE           VALUE
-----------------     --------       ----------- 
open_cursors         integer       300

SQL> alter system set open_cursors=200 scope=memory;

System altered.

SQL> show parameter open_cursors

NAME                    TYPE           VALUE
-----------------     --------       ----------- 
open_cursors         integer       200

【DEFERRED】延迟参数修改

重新连接生效

SQL> show parameter backup_tape_io_slaves

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
backup_tape_io_slaves boolean TRUE
SQL> alter system set backup_tape_io_slaves=false DEFERRED;

System altered.

SQL> show parameter backup_tape_io_slaves

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
backup_tape_io_slaves boolean TRUE

SQL> alter system set backup_tape_io_slaves=true DEFERRED sid='*';

System altered.

SQL> alter system set backup_tape_io_slaves=true DEFERRED sid='pri';

System altered.

SQL> alter system set backup_tape_io_slaves=true scope=memory DEFERRED sid='pri';
alter system set backup_tape_io_slaves=true scope=memory DEFERRED sid='pri'
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

2. alter system reset 

集群：alter system reset parameter scope=memory | spfile | both sid='sid|*';

SQL> alter system reset open_cursors scope=spfile sid='pri';
alter system reset open_cursors scope=spfile sid='pri'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system reset open_cursors scope=spfile sid='*';

System altered.

SQL> alter system reset open_cursors scope=memory sid='pri';
alter system reset open_cursors scope=memory sid='pri'
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported


SQL> alter system reset open_cursors scope=memory sid='*';
alter system reset open_cursors scope=memory sid='*'
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported


SQL> alter system reset open_cursors scope=both sid='pri';
alter system reset open_cursors scope=both sid='pri'
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported


SQL> alter system reset open_cursors scope=both sid='*';
alter system reset open_cursors scope=both sid='*'
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported