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

ORA-04031: unable to allocate n 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 initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.

Reference: Oracle Documentation

I have seen a alot of Oracle DBA to bluntly say, if you encounter ORA-04031 then Increase SHARED_POOL_SIZE parameter and restart the database. With due respect to them, Yes it is one of the solution of ORA-04031, but should not we first identify the issue and then suggest an action. ORA-04031 could be due to another reasons and not simply because the shared pool is too small.

Understand ORA-04031:
When a process tries to allocate contiguous memory in the shared pool and fails to find memory required, then it would lead to ORA-04031. There could be many reasons for ORA-04031, some of the important are following
- Inadequate Sizing of Shared Pool
- Not using bind variables
- Not limiting number of rows in BULK COLLECT
- Too many pinned packages
- Objects Candidate for Pinning but not Pinned

Unfortunately, Most of the application developers do not care about using Database efficiently and abuse shared pool by not using bind variables and as a result generating a unique SQL statement for every single database call made. Extra memory shared pool may supress ORA-04031 for some time, eventually the problem will reoccur if you don't deal with the source of the problem.

In this case you will need to look at temporary workarounds until the SQL abusing applications are fixed, you can use one of the following:
- forcing cursor sharing by "alter system set cursor_sharing='force' scope=both" and bounce instance.
- flushing shared pool by doing "alter system flush shared pool"
- Heavy fragmentation of shared pool can also be fixed by bouncing the instance.

I have also seen ORA-04031 due to not using LIMIT clause in BULK COLLECT in case when Automatic Memory Management was configured. ORA-04031 also rises when too many objects are pinned in Shared Pool and space is not adequately left for running processes.

Tips to avoid ORA-04031:
- Always use Bind Variables.
- Use LIMIT clause with BULK COLLECT
- Pin required and most used objects in Shared Pool
- Reduce un-necessary use of Shared Pool
- Use Automatic Memory Management or Automatic SGA Management

Yes, the ultimate solution is to increase shared_pool_size and/or shared_pool_reserved_size or SGA_TARGET/MEMORY_TARGET.

I hope this was an useful information to you, please provide your feedback.


Related Links
- ORA-27101: shared memory realm does not exist
- ORA-00600 internal error code
- ORA-01034: ORACLE not available
- ORA-00849: SGA_TARGET cannot be set to more than MEMORY_MAX_TARGET
- ORA-00018 maximum number of sessions exceeded
- ORA-00020 maximum number of processes exceeded