Question: When executing a procedure I am getting “ORA-04031 Unable to Allocate Shared Memory” error. How do I avoid this error?
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;
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.