测试memory_max_target

                   由于对下题答案的疑问，做了相关的测试；
证明当memory_max_target设置为0时，memory_target设置有值，memory_max_target的值会自动变为memory_target的当前设置值；

QUESTION 161

You set the following parameters in the parameter file and restarted the database:

MEMORY_MAX_TARGET=0

MEMORY_TARGET=500M

PGA_AGGREGATE_TARGET=90M

SGA_TARGET=270M

Which two statements are true regarding these parameters after the database instance is restarted? (Choose two.)

A. The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB and 270 MB,

respectively.

B. The value of the MEMORY_MAX_TARGET parameter remains zero till it is changed manually.

C. The MEMORY_MAX_TARGET parameter is automatically set to 500 MB.

D. The PGA_AGGREGATE_TARGET and SGA_TARGET parameters are automatically set to zero.

Answer: AC


具体测试过程如下：


当前环境
s11:/backup # su - oracle
oracle@s11:~> sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 9 17:57:26 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

sys@ORCL> show parameter memory

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         integer     0
memory_max_target             big integer 1000M
memory_target                 big integer 1000M
shared_memory_address             integer     0

sys@ORCL> show parameter sga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
lock_sga                 boolean     FALSE
pre_page_sga                 boolean     FALSE
sga_max_size                 big integer 900M
sga_target                 big integer 0
sys@ORCL> show parameter pga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 0


测试1：
直接修改memory_max_target值为0，重启库会报错；
alter system set memory_max_target=0 scope=spfile;
shutdown immediate
startup
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

重新启动数据库；
shutdown immediate
startup pfile='/backup/pfile308.ora'
create spfile from pfile='/backup/pfile308.ora';
shutdown immediate
startup

测试2：
修改如下参数，重启数据库
alter system set sga_target=800M scope=spfile;
alter system set pga_aggregate_target=200M scope=spfile;
alter system set memory_max_target=0 scope=spfile;
alter system set memory_target=0 scope=spfile;
startup force
报错：
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 943718400 cannot be set to more than MEMORY_MAX_TARGET 0.


测试3：
必须重置memory_target和memory_max_target的值，不能直接设置为0；
alter system reset memory_target;
alter system reset memory_max_target;

startup force

sys@ORCL> show parameter sga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
lock_sga                 boolean     FALSE
pre_page_sga                 boolean     FALSE
sga_max_size                 big integer 900M
sga_target                 big integer 0

sys@ORCL> show parameter pga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 10M

sys@ORCL> show parameter mem

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         integer     0
memory_max_target             big integer 0
memory_target                 big integer 0
shared_memory_address             integer     0


测试4：
设置memory_target为1300M，而memory_max_target不设，重启库；可看到memory_max_target的值和memory_target相同；
sys@ORCL> alter system set memory_target=1300m scope=spfile;
sys@ORCL> show parameter mem

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         integer     0
memory_max_target             big integer 1300M
memory_target                 big integer 1300M
shared_memory_address             integer     0

sys@ORCL> show parameter sga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
lock_sga                 boolean     FALSE
pre_page_sga                 boolean     FALSE
sga_max_size                 big integer 900M
sga_target                 big integer 0

sys@ORCL> show parameter pga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 0


测试5：
将memory_target从1300M调小为1000M；重启库；可看到memory_max_target的值和memory_target相同；
sys@ORCL> alter system set memory_target=1000M scope=spfile;


sys@ORCL> show parameter mem

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         integer     0
memory_max_target             big integer 1000M
memory_target                 big integer 1000M
shared_memory_address             integer     0
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29519108/viewspace-2052049/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29519108/viewspace-2052049/