Cry about...
Oracle Troubleshooting

ORA-04031: unable to allocate … shared memory

Symptom:

The Oracle error:

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

Cause:

More shared memory is needed than was allocated. SGA private memory has been exhausted.

Fragmentation of shared pool memory is a common problem and ORA-04031 is commonly a result of such fragmentation. Application programmers usually get this error while attempting to load a big package or while executing a very large procedure and there is not sufficient contiguous free memory available in the shared pool. This may be due to fragmentation of the shared pool memory or insufficient memory in the shared pool.

Possible remedies:

• Use the dbms_shared_pool package to pin large packages.

• Attempt to reduce the use of shared memory.

• Increase the initialisation parameter ‘SHARED_POOL_SIZE’.

Alternate symptom

An error of the form (Oracle 8.1.5):

ORA-04031: unable to allocate NNNNN bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")

Cause:

This error indicates that Oracle is unable to allocate memory from the ‘large’ pool.

To determine the number of free bytes in the ‘large’ pool execute the following SQL:

SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);

Possible Remedy:

• To resolve the problem, consider increasing the value for ‘LARGE_POOL_SIZE’.