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

The ORA-04031 unable to allocate … bytes of shared memory might cause some irritation when it reports that a (relatively small) amount of memory cannot be allocated even though there is plenty of free memory in the System.

This is because the Shared pool heap manager allocates contiguous memory and the largest chunk of contiguous memory might be well below the total amount of free memory.

An ORA-04031 error is often related to

• the value of shared_pool_size and/or
• the database not finding large enough chunks of memory because of fragmentation

The most obvious fix for ORA-04031 errors is to increase the size of the Shared Pool. When doing that, it might be useful to consult v$shared_pool_advice.

Others solutions include:

• Reducing the value of session_cached_cursors
• Reducing the value of cursor_space_for_time

alter session set events '4031 trace name errorstack level 3';

alter session set events '4031 trace name heapdump level 536870914';