OCP每日一题
Examine the current value for the following parameters in your database instance:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 700M
DB_8K_CACHE_SIZE = 124M
LOG_BUFFER = 200M
You issue the following command to increase the value of DB_8K_CACHE_SIZE:
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;
Which statement is true?
A. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
B. It succeeds only if memory is available from the autotuned components if SGA.
C. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET.
D. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.
题解：
Answer：B
首先了解：“DB_nK_CACHE_SIZE”参数 “DB_nK_CACHE_SIZE”参数的取值有很多种，有2k、4k、8k、16k、32k。在设置此参数时，我们需要注意的是，与数据库默认的块尺寸相同的那个参数是不能被设定的。例如，如果数据库的默认块尺寸是8k，那么当我们尝试设置“DB_8K_CACHE_SIZE”参数时便会遭遇报错（报错内容：ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size）。这是由于这个信息已经体现在“db_cache_size”参数上。
测试(使用db_16k_cache_size来测试):
SQL> alter system set db_8k_cache_size=100m;
alter system set db_8k_cache_size=100m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
SQL>
默认是8k的数据库，所以报错
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1G
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 700M
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer big integer 200M
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
data_transfer_cache_size big integer 0
db_16k_cache_size big integer 124M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> alter system set db_16k_cache_size=140m;
System altered.
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
data_transfer_cache_size big integer 0
db_16k_cache_size big integer 140M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL>
SQL> alter system set db_16k_cache_size=300m;
alter system set db_16k_cache_size=300m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
SQL>