SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> !uname -a
Linux rac1 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

 

 

1,动态参数的reset

SQL> select name,value,issys_modifiable from v$parameter where name='open_cursors';

NAME                 VALUE                          ISSYS_MOD
-------------------- ------------------------------ ---------
open_cursors         400                            IMMEDIATE

SQL> alter system set open_cursors=500 scope=both;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
SQL> alter system reset open_cursors;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2216944 bytes
Variable Size             515902480 bytes
Database Buffers          209715200 bytes
Redo Buffers                2879488 bytes
Database mounted.
Database opened.
SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     400

 

2,静态参数的reset

SQL> select name,value,issys_modifiable from v$parameter where name='sessions';

NAME                 VALUE                          ISSYS_MOD
-------------------- ------------------------------ ---------
sessions             247                            FALSE

SQL> alter system set sessions=255 scope=spfile;

System altered.

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     247
shared_server_sessions               integer
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2216944 bytes
Variable Size             515902480 bytes
Database Buffers          209715200 bytes
Redo Buffers                2879488 bytes
Database mounted.
Database opened.
SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     255
shared_server_sessions               integer
SQL> alter system reset sessions;

System altered.

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     255
shared_server_sessions               integer
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2216944 bytes
Variable Size             515902480 bytes
Database Buffers          209715200 bytes
Redo Buffers                2879488 bytes
Database mounted.
Database opened.
SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     247
shared_server_sessions               integer

通过上面两个例子我们可以看书,使用reset命令修改参数到默认值是需要重启数据库才能生效的。

其实alter sysetm reset 语句是有scope子句,但scope只能是spfile,而both,memory参数是不存在的,也就是我们执行reset操作时都默认带了scope=spfile子句,这也是为什么必须重启才生效的原因了。

下面是引自官方文档:

Clearing Initialization Parameter Values
You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any
initialization parameter in the SPFILE that was used to start the instance. Neither
SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not
required, but can be included.
You may want to clear a parameter in the SPFILE so that upon the next database
startup a default value is used.