Question

ORA-04031: unable to allocate

Answer 1

Sharing Memory—Automatically

before Oracle9i Database Release 2, adjusting the pool sizes required bouncing the server—hardly practical in a production environment.

That's why Oracle Database 10g's Automatic Shared Memory Management (ASMM) is such a welcome improvement.ASMM automatically sizes many of the memory pools while the database is running, allocating and de-allocating memory as needed

SQL> alter system set sga_target=160M scope=both;
System altered.

SQL> alter system set db_cache_size=0;
System altered.

SQL> alter system set shared_pool_size=0;
System altered.

Query of V$SGA_TARGET_ADVICE 

                               
SQL> select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads
 from v$sga_target_advice order by sga_size_factor;

Answer 2

In 11g -

set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database.

 

select value from v$pgastat where name='maximum PGA allocated';

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

ALTER SYSTEM SET MEMORY_TARGET = nM;
ALTER SYSTEM SET SGA_TARGET = 0;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;

 

Note:

The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.