What would happen the following command to increase the value of DB_8K_CACHE_SIZE?

Note the following parameters settings in your database:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 700M
DB_8K_CACHE_SIZE = 124M
LOG_BUFFER = 200M
You issued the following command to increase the value of DB_8K_CACHE_SIZE:
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;
What would happen?

Note the following parameters settings in your database:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 700M
DB_8K_CACHE_SIZE = 124M
LOG_BUFFER = 200M

You issued the following command to increase the value of DB_8K_CACHE_SIZE:
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;

What would happen?

A.
It will fail because DB_8K_CACHE_SIZE parameter cannot be changed dynamically

B.
It will be successful only if the memory is available from the auto tuned components

C.
It will fail because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET

D.
It will fail because an increase in the DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE

Explanation:
Whoever wrote this question is on dope.. log_buffer=200M? How does one set that?

http://www.dba-oracle.com/art_tr_multiblock.htm

Allocating many RAM data buffers

In Oracle, you can start using new RAM buffers at any time. However, when you add  space to a new data buffer, you must make sure that RAM is available within the Oracle  SGA. Otherwise, you’ll get this error:

SQL> alter system set db_16k_cache_size=10m;

alter system set db_16k_cache_size=10m
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

To get around this problem, you can reduce the size of an existing RAM region or tell  Oracle to increase the SGA size. Increasing the total size of the RAM SGA is  accomplished with this simple command:

alter system set sga_max_size=130m scope=spfile;

Now that you have room to add frames to a new pool, add a new data buffer, and issue  an alter system command, like so:

alter system set db_16k_cache_size=1028576;
System Altered.

You can verify that this new buffer exists by viewing the current Oracle parameters with  this command:

SQL> show parameters cache_size



Leave a Reply 4

Your email address will not be published. Required fields are marked *


ocp student

ocp student

Right answer is B not D.
It will be successful only if the memory is available from the auto tuned components
although it could also fail if an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET.

Tresla

Tresla

Right answer is A.

Barbaros

Barbaros

Why? there’s no clue about db_block_size = 8K.

dbr

dbr

you already have a 8K buffer cache set so A its out of question