ORA-04031 Unable to Allocate Shared Memory

Question: When executing a procedure I am getting “ORA-04031 Unable to Allocate Shared Memory” error. How do I avoid this error?

BEGIN
*
ERROR at line 1:
ORA-04031: unable to allocate 4096 bytes of shared memory (“java pool”,”java/util/SortedMap”,”JOXLE^47e14412″,”:SGAClass”)
ORA-06512: at line 3
IF CatbundleCreateDir(:catbundleLogDir) = 0 THEN
*
ERROR at line 71:
ORA-06550: line 71, column 14:
PLS-00201: identifier ‘CATBUNDLECREATEDIR’ must be declared
ORA-06550: line 71, column 11:
PL/SQL: Statement ignored


Answer: According to Oracle the cause of the ORA error is below.

ORA-04031 unable to allocate string bytes of shared memory (“string”,”string”,”string”,”string”)
More shared memory is needed than was allocated in the shared pool.

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.

The cause can also be fragmentation of the Shared Pool area. Due to this fragmentation large object cannot be stored in the Shared Pool.

A temporary fix would be to flush the shared pool.

SQL> alter system flush shared_pool;
System altered

Additional physical RAM may be needed or other areas of the SGA can be shrunk if possible.

Once the RAM is added, the shared_pool_size and shared_pool_reserved_size parameters should be increased.


Note Detail: This error can commonly occur due to the SHARED POOL SIZE. This error can be due to an inadquate sizing of the SHARED POOL or due to   fragmentation of the shared pool. ORA-04031 error is a result of lack of contiguous space in the library cache.Checked the logfile and found that it occurs  two times for today's date. Can you please re-run the process and check if the issue still there. Please run the process when db is less busy may be off  hours. Also as per our earlier communication over the parameter change we are overall increasing the SGA size so it may resolve the issue since Automatic  memory management will be enforced on this.

SELECT NAME, VALUE/(1024*1024) SHARED_POOL_SIZE_MB FROM V$PARAMETER WHERE NAME LIKE '%shared_pool%';

You can find the free space in Shared pool with the query: 

 SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB 
 FROM V$SGASTAT WHERE POOL='shared pool'
 AND NAME='free memory'

 ORDER BY BYTES DESC;