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

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1

< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

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 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. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.

I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer: MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,

http://oracleracexpert.com