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

Oracle Database Tips by Donald BurlesonJuly 17, 2015 - Updated February 24, 2016

Question: I am getting the error ORA-04031 Cannot allocate shared memory. I've tried increasing my init.ora shared_pool_size, but to no avail. What are some causes for the ORA-04031 error, and how do I fix it?

Answer: The ORA-04031 error has many root causes. Also, see MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Heavy fragmentation of the shared pool - This can be fixed by increasing the shared_pool_size or doing a "alter system flush shared pool" or bouncing the instance.
Too many pinned packages - If you have pinned lots of packages with dbms_shared_pool.keep, they have not leave enough room for new work.

Ultimately the solution to a ORA-04031 error is adding RAM to shared_pool_size and/or shared_pool_reserved_size. In 11g and beyond , if using AMM, increase memory_max_size to resolve the ORA-04031 error.

The Oracle documentation has these notes on the ORA-04031 error:

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

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

The ORA-04031 error also happens when you are using automatic memory management (sga_max_size), and the memory specific is too small.

This is the BC Oracle DBA Scripts collection with Oracle DBA Scripts for tuning, monitoring, a professional download of over 600 Oracle DBA Scripts.

��