Question

While the database instance is up and running, you receive an out-of-memory error due to the under-sizing of the shared pool. You checked the trace file and observed that the following error was always recorded during peak hours:

ORA-04031: unable to allocate 4000 bytes of shared memory

On investigation, you found that SGA_MAX_SIZE was larger than the sum of the sizes of all System Global Area (SGA) components and you do not have the scope to increase it further. Identify a solution to reduce the probability of getting this error in future.

(a) Set the PRE_PAGE_SGA parameter to TRUE

(b) Set the LOCK_SGA initialization parameter to TRUE

(c) Implement Automatic Shared Memory Management

(d) Set the SGA_TARGET initialization parameter to zero

The question was posed to me in exam.

Asked question is from Installing Oracle Database 11g and Creating a Database topic in chapter Oracle Database 11g Architecture Options of Oracle

Answer 1

Correct choice is (c) Implement Automatic Shared Memory Management

For explanation: Cause: More shared memory is needed than was allocated in the shared pool.Action : If the shared pool is out of memory, either use the DBMS_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters