Oracle reset初始化参数

Oracle reset初始化参数

 

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

   www.2cto.com 

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.