Examine the memory-related parameters set in the SPFILE of an Oracle database:
memory_max_target--6G
memory_target=5G
pga_aggregate_target=500M
sga_max_size=0
sga_target=0

Which statement is true?

A. Only SGA components are sized automatically-
B. Memory is dynamically re-allocated between the SGA and PGA as needed.
C. The size of the PGA cannot grow automatically beyond 500 MB.
D. The value of the MEMORY_TARGET parameter cannot be changed dynamically.

---

Answer:

First, let's start with some background information. An Oracle database is a collection of data that is stored on a computer system. When the database is in use, it needs to allocate memory to different parts of the system in order to function properly. The memory-related parameters that are set in the SPFILE (System Parameter File) determine how much memory is allocated to each component of the database.

Now, let's take a closer look at the specific parameters listed in the question:

memory_max_target: This parameter sets the maximum amount of memory that can be allocated to the database. In this case, the maximum amount is set to 6 gigabytes (6G).

memory_target: This parameter sets the amount of memory that is allocated to the database. In this case, 5 gigabytes (5G) of memory are allocated.

pga_aggregate_target: This parameter sets the amount of memory that is allocated to the PGA (Program Global Area), which is a part of the database that is used for storing data related to individual user sessions. In this case, 500 megabytes (500M) of memory are allocated to the PGA.

sga_max_size: This parameter sets the maximum size of the SGA (System Global Area), which is a part of the database that is used for storing data related to the entire system. In this case, the maximum size is set to 0, which means that there is no maximum size limit.

sga_target: This parameter sets the amount of memory that is allocated to the SGA. In this case, the SGA is not allocated any memory, since the value is set to 0.

Now, let's look at the question itself. The question asks which statement is true, and provides four possible options:

A. Only SGA components are sized automatically: This statement is not true, since both the SGA and the PGA can be automatically sized based on the values of the memory-related parameters.

B. Memory is dynamically re-allocated between the SGA and PGA as needed: This statement is partially true. Memory can be dynamically re-allocated between the SGA and PGA, but only if the sum of the values of sga_target and pga_aggregate_target is less than or equal to the value of memory_target.

C. The size of the PGA cannot grow automatically beyond 500 MB: This statement is true. The value of pga_aggregate_target sets a hard limit on the amount of memory that can be allocated to the PGA, and this limit cannot be exceeded automatically.

D. The value of the MEMORY_TARGET parameter cannot be changed dynamically: This statement is not true. The value of memory_target can be changed dynamically, as long as the new value is within the range of memory_max_target and the sum of sga_target and pga_aggregate_target.

So, the correct answer to the question is C: The size of the PGA cannot grow automatically beyond 500 MB.

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips