SOLVED

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

Asked by OracleDba••13 views•oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
ORA-04031: unable to allocate nn bytes of shared memory

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

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:

1.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.

2.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
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
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.
OracleDba•

Post Your Solution