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
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_pool package 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
To determine the number of free bytes in the ‘large’ pool execute the
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.