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:
Allocating many RAM data buffersIn 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
co mand: 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
I think that “B” is correct.
Modifying Parameters for Manually Sized Components
Parameters for manually sized components can be dynamically altered as well. However, rather than setting a minimum size, the value of the parameter specifies the precise size of the corresponding component. When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. When you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components.
For example, consider this configuration:
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken away from the automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M is given to the automatically sized components.
D