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.

Leave a Reply

Your email address will not be published.