ORA-04031: unable to allocate … shared memory
The Oracle error:
ORA-04031: unable to allocate nnn bytes of shared memory
More shared memory is needed than was allocated. SGA private memory has been exhausted.
Fragmentation of shared pool memory is a common problem and
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.
- Use the
dbms_shared_poolpackage to pin large packages.
- Attempt to reduce the use of shared memory.
- Increase the initialisation parameter ‘
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")
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);
- To resolve the problem, consider increasing the value for ‘
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.