【参数】动态参数、静态参数区别及测试

xixuefeng Oracle, Parameter 2012-09-26 22:05:33  760 次浏览  已关闭评论

一：静态参数、动态参数的概念

1）静态参数：在修改参数以后，必须重启数据才能生效的参数

## 如下sql来查询所有的静态参数：
select name,value from v$parameter where issys_modifiable = upper('false');

1 2	## 如下sql来查询所有的静态参数： select name,value from v$parameter where issys_modifiable = upper('false');

2)动态参数：在修改参数以后，不用重启也可以生效且重启后依然生效的参数

## 如下sql来查询所有的动态参数：
select name,value from v$parameter where issys_modifiable <> upper('false');

1 2	## 如下sql来查询所有的动态参数： select name,value from v$parameter where issys_modifiable <> upper('false');

二：静态参数与动态参数的区别，如下表格：

参数类型	scope=spfile	scope= memory	scope= both	deferred
静态参数	重启数据库生效	不允许使用	不允许使用	不允许使用
动态参数(immediate)	重启数据库生效	立即生效,重启数据库失效	立即生效,重启数据库依然有效	不允许使用
动态参数(deferred)	重启数据库生效	不允许使用	不允许使用	允许使用

通过上图可以很清楚的区分它们之间的区别，特别注意的是：

## 在动态参数中还有两种属性
immediate：
select name,value from v$parameter where issys_modifiable=upper('immediate');
deferred：
select name,value from v$parameter where issys_modifiable=upper('deferred');

## 通过下面语句我们也可以看出来，issys_modifiable有三个值（false即为静态参数，其他为动态参数）
SYS@ORA11GR2> select issys_modifiable,count(*) from v$parameter group by issys_modifiable;

ISSYS_MODIFIABLE       COUNT(*)
-------------------- ----------
IMMEDIATE                   224
FALSE                       110
DEFERRED                      8

SYS@ORA11GR2>

## 在动态参数中还有两种属性

immediate：

select name,value from v$parameter where issys_modifiable=upper('immediate');

deferred：

select name,value from v$parameter where issys_modifiable=upper('deferred');

## 通过下面语句我们也可以看出来，issys_modifiable有三个值（false即为静态参数，其他为动态参数）

SYS@ORA11GR2> select issys_modifiable,count(*) from v$parameter group by issys_modifiable;

ISSYS_MODIFIABLE COUNT(*)

-------------------- ----------

IMMEDIATE 224

FALSE 110

DEFERRED 8

SYS@ORA11GR2>

这两种类型都属于动态参数，因为都符合动态参数的定义，不过 issys_modifiable=deferred 的参数的属性更像静态参数（在scope子句中，只允许 scope=spfile，并且重启后才生效），不过与之不同的是，这种类型的参数允许使用 deferred 子句（只有这种类型的参数才允许使用deferred子句），意思是，修改完成后，当前会话不生效，新开启的会话中参数修改才生效，且重启后依然生效（符合动态参数的定义）。

三：静态参数测试

## 静态参数在修改时必须使用 scope = spfile
SYS@ORA11GR2> show parameter processes

NAME                             TYPE   VALUE
------------------------- ----------- -------
aq_tm_processes               integer       0
db_writer_processes           integer       1
gcs_server_processes          integer       0
global_txn_processes          integer       1
job_queue_processes           integer    1000
log_archive_max_processes     integer       4
processes                     integer     150
SYS@ORA11GR2>
SYS@ORA11GR2> alter system set processes=200;
alter system set processes=200
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SYS@ORA11GR2> alter system set processes=200 scope=spfile;

System altered.

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


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


SYS@ORA11GR2> alter system set processes=201 deferred;
alter system set processes=201 deferred
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SYS@ORA11GR2>

## 静态参数在修改时必须使用 scope = spfile

SYS@ORA11GR2> show parameter processes

NAME TYPE VALUE

------------------------- ----------- -------

aq_tm_processes integer 0

db_writer_processes integer 1

gcs_server_processes integer 0

global_txn_processes integer 1

job_queue_processes integer 1000

log_archive_max_processes integer 4

processes integer 150

SYS@ORA11GR2>

SYS@ORA11GR2> alter system set processes=200;

alter system set processes=200

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SYS@ORA11GR2> alter system set processes=200 scope=spfile;

System altered.

SYS@ORA11GR2> alter system set processes=201 scope=memory;

alter system set processes=201 scope=memory

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SYS@ORA11GR2> alter system set processes=201 scope=both;

alter system set processes=201 scope=both

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SYS@ORA11GR2> alter system set processes=201 deferred;

alter system set processes=201 deferred

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SYS@ORA11GR2>

四：动态参数（immediate）测试

注：issys_modifiable为immediate;

oracle10g job_queue_processes默认值为10，oracle11g job_queue_processes默认值为1000

1)初始默认值为1000

SYS@ORA11GR2>show parameter job_queue_processes

NAME                         TYPE         VALUE
--------------------- ----------- -------------
job_queue_processes       integer          1000
SYS@ORA11GR2>

SYS@ORA11GR2>show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 1000

SYS@ORA11GR2>

2)将其修改为500，不加scope子句(等同于scope=both，修改即生效，重启后依然生效)

SYS@ORA11GR2> alter system set job_queue_processes=500;

System altered.

SYS@ORA11GR2> show parameter job_queue_processes

NAME                         TYPE         VALUE
--------------------- ----------- -------------
job_queue_processes       integer           500
SYS@ORA11GR2>
SYS@ORA11GR2> startup force;
ORACLE instance started.

Total System Global Area  853716992 bytes
Fixed Size                  1339852 bytes
Variable Size             645926452 bytes
Database Buffers          201326592 bytes
Redo Buffers                5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2> show parameter job_queue_processes

NAME                         TYPE         VALUE
--------------------- ----------- -------------
job_queue_processes       integer           500
SYS@ORA11GR2>

SYS@ORA11GR2> alter system set job_queue_processes=500;

System altered.

SYS@ORA11GR2> show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 500

SYS@ORA11GR2>

SYS@ORA11GR2> startup force;

ORACLE instance started.

Total System Global Area 853716992 bytes

Fixed Size 1339852 bytes

Variable Size 645926452 bytes

Database Buffers 201326592 bytes

Redo Buffers 5124096 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 500

SYS@ORA11GR2>

3)将其修改为600，scope=spfile(修改后不立即生效，重启后生效)

SYS@ORA11GR2> alter system set job_queue_processes=600 scope=spfile;

System altered.

SYS@ORA11GR2>
SYS@ORA11GR2> show parameter job_queue_processes                                 
NAME                         TYPE         VALUE
--------------------- ----------- -------------  
job_queue_processes       integer           500
SYS@ORA11GR2>
SYS@ORA11GR2>startup force;
ORACLE instance started.

Total System Global Area  853716992 bytes
Fixed Size                  1339852 bytes
Variable Size             641732148 bytes
Database Buffers          205520896 bytes
Redo Buffers                5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2> show parameter job_queue_processes

NAME                         TYPE         VALUE
--------------------- ----------- -------------
job_queue_processes       integer           600
SYS@ORA11GR2>

SYS@ORA11GR2> alter system set job_queue_processes=600 scope=spfile;

System altered.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 500

SYS@ORA11GR2>

SYS@ORA11GR2>startup force;

ORACLE instance started.

Total System Global Area 853716992 bytes

Fixed Size 1339852 bytes

Variable Size 641732148 bytes

Database Buffers 205520896 bytes

Redo Buffers 5124096 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 600

SYS@ORA11GR2>

4)将其修改为700，scope=memory(修改后立即生效，重启后失效)

SYS@ORA11GR2> alter system set job_queue_processes=700 scope=memory;

System altered.

SYS@ORA11GR2>
SYS@ORA11GR2> show parameter job_queue_processes

NAME                         TYPE         VALUE
--------------------- ----------- -------------
job_queue_processes       integer           700
SYS@ORA11GR2>
SYS@ORA11GR2> startup force;
ORACLE instance started.

Total System Global Area  853716992 bytes
Fixed Size                  1339852 bytes
Variable Size             641732148 bytes
Database Buffers          205520896 bytes
Redo Buffers                5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2> show parameter job_queue_processes

NAME                         TYPE         VALUE
--------------------- ----------- -------------
job_queue_processes       integer           600
SYS@ORA11GR2>

SYS@ORA11GR2> alter system set job_queue_processes=700 scope=memory;

System altered.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 700

SYS@ORA11GR2>

SYS@ORA11GR2> startup force;

ORACLE instance started.

Total System Global Area 853716992 bytes

Fixed Size 1339852 bytes

Variable Size 641732148 bytes

Database Buffers 205520896 bytes

Redo Buffers 5124096 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter job_queue_processes

NAME TYPE VALUE

--------------------- ----------- -------------

job_queue_processes integer 600

SYS@ORA11GR2>

5)将其修改为1000，deferred (修改后当前会话无效，新建立会话生效) issys_modifiable为immediate的动态参数无法使用deferred

SYS@ORA11GR2> alter system set job_queue_processes=1000 deferred;
alter system set job_queue_processes=1000 deferred
                                          *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SYS@ORA11GR2>

SYS@ORA11GR2> alter system set job_queue_processes=1000 deferred;

alter system set job_queue_processes=1000 deferred

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this

option

SYS@ORA11GR2>

五：动态参数（deferred）测试

注：issys_modifiable为deferred;

1)初始默认值为65536

SYS@ORA11GR2> show parameter sort_area_size

NAME                        TYPE           VALUE
-------------------- ----------- ---------------
sort_area_size           integer           65536
SYS@ORA11GR2>

SYS@ORA11GR2> show parameter sort_area_size

NAME TYPE VALUE

-------------------- ----------- ---------------

sort_area_size integer 65536

SYS@ORA11GR2>

2)修改sort_area_size为60000，使用scope=both子句（执行结果：不允许使用scope=both）

SYS@ORA11GR2> alter system set sort_area_size=60000 scope=both;
alter system set sort_area_size=60000 scope=both
                                  *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SYS@ORA11GR2>

SYS@ORA11GR2> alter system set sort_area_size=60000 scope=both;

alter system set sort_area_size=60000 scope=both

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this

option

SYS@ORA11GR2>

3)修改sort_area_size为60000，使用scope= memory子句（执行结果：不允许使用scope= memory）

SYS@ORA11GR2> alter system set sort_area_size=60000 scope=memory;
alter system set sort_area_size=60000 scope=memory
                                  *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SYS@ORA11GR2>

SYS@ORA11GR2> alter system set sort_area_size=60000 scope=memory;

alter system set sort_area_size=60000 scope=memory

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this

option

SYS@ORA11GR2>

4)修改sort_area_size为60000，使用scope= spfile子句（执行结果：修改成功，重启生效）

SYS@ORA11GR2> alter system set sort_area_size=60000 scope=spfile;

System altered.

SYS@ORA11GR2>
SYS@ORA11GR2> show parameter sort_area_size

NAME                        TYPE           VALUE
-------------------- ----------- ---------------
sort_area_size           integer           65536
SYS@ORA11GR2>
SYS@ORA11GR2> startup force;
ORACLE instance started.

Total System Global Area  853716992 bytes
Fixed Size                  1339852 bytes
Variable Size             641732148 bytes
Database Buffers          205520896 bytes
Redo Buffers                5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2> show parameter sort_area_size

NAME                        TYPE           VALUE
-------------------- ----------- ---------------
sort_area_size           integer           60000
SYS@ORA11GR2>

SYS@ORA11GR2> alter system set sort_area_size=60000 scope=spfile;

System altered.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter sort_area_size

NAME TYPE VALUE

-------------------- ----------- ---------------

sort_area_size integer 65536

SYS@ORA11GR2>

SYS@ORA11GR2> startup force;

ORACLE instance started.

Total System Global Area 853716992 bytes

Fixed Size 1339852 bytes

Variable Size 641732148 bytes

Database Buffers 205520896 bytes

Redo Buffers 5124096 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter sort_area_size

NAME TYPE VALUE

-------------------- ----------- ---------------

sort_area_size integer 60000

SYS@ORA11GR2>

5)修改sort_area_size为66666，使用deferred子句（执行结果：新连接的会话生效，在修改之前连接的会话都不生效）

SYS@ORA11GR2> alter system set sort_area_size=66666 deferred;

System altered.

SYS@ORA11GR2>
SYS@ORA11GR2> show parameter sort_area_size

NAME                        TYPE           VALUE
-------------------- ----------- ---------------
sort_area_size           integer           60000
SYS@ORA11GR2>
SYS@ORA11GR2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocmu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 13:19:51 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2> show parameter sort_area_size

NAME                        TYPE           VALUE
-------------------- ----------- ---------------
sort_area_size           integer           66666
SYS@ORA11GR2>

SYS@ORA11GR2> alter system set sort_area_size=66666 deferred;

System altered.

SYS@ORA11GR2>

SYS@ORA11GR2> show parameter sort_area_size

NAME TYPE VALUE

-------------------- ----------- ---------------

sort_area_size integer 60000

SYS@ORA11GR2>

SYS@ORA11GR2> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ocmu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 13:19:51 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2> show parameter sort_area_size

NAME TYPE VALUE

-------------------- ----------- ---------------

sort_area_size integer 66666

SYS@ORA11GR2>

小结：

1）静态参数必须使用必须重启才能生效，即使用scope=spifle子句;

2）只要使用scope=spifle子句修改的参数，就必须重启才能生效（无论是动态还是静态）；

3）在修改参数时如果不使用scope子句，那么默认为使用scope=both子句；

4）动态参数分为两种，一种可以使用deferred子句（即：issys_modifiable=upper(‘deferred’) ） ,一种不可以使用deferred子句（即：issys_modifiable=upper(‘immediate’)）；

5）不可以使用deferred子句的动态参数，scope可以使用任何一种类型的值，可以是用deferred子句的参数，scope只允许等于spfile；

上一篇：【Oracle】【延迟约束】的应用

下一篇：【unused】恢复unused的字段

【参数】动态参数、静态参数区别及测试

分类目录

文章归档

功能

2024年4月
一	二	三	四	五	六	日
« 11月
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

【参数】动态参数、静态参数区别及测试

和本文关系暧昧的

分类目录

文章归档

功能