測試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