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?
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
- 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.
The Oracle documentation has these notes on the
ORA-04031: unable to allocate nn bytes of
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.