ORA-04301: UNABLE TO ALLOCATE XXX BYTES OF SHARED MEMORY

 Error

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

Solution

1. Need to add more memory to the Shared pool area.

Check the size of memory allocate to shared_pool_size, shared_pool_reserved_size and large_pool_size.

SQL> show parameter pool

NAME                          TYPE        VALUE
----------------------------- ----------- --------
buffer_pool_keep              string
buffer_pool_recycle           string
java_pool_size                big integer 0
large_pool_size               big integer 0
memoptimize_pool_size         big integer 0
olap_page_pool_size           big integer 0
shared_pool_reserved_size     big integer 16M
shared_pool_size              big integer 0
streams_pool_size             big integer 0

2. Increasing the value of the INIT.ORA parameters “shared_pool_reserved_size”, “shared_pool_size” and “large_pool_size”.

ALTER SYSTEM SET shared_pool_size = 1g scope=both;
ALTER SYSTEM SET large_pool_size=100M scope=both;
ALTER SYSTEM SET shared_pool_reserved_size=512M scope=spfile;

3. If it not set then increase the SGA_TARGET or MEMORY_TARGET parameter as you set which will allocate more space to the Shared POOL size.

SQL> show parameter sga_target

NAME            TYPE        VALUE
--------------- ----------- ------------------------------
sga_target      big integer 1536M
SQL> show parameter memory_target

NAME            TYPE        VALUE
--------------- ----------- ------------------------------
memory_target   big integer 0
-- Increase the SGA_TARGET or MEMORY_TARGET effect on next startup
ALTER SYSTEM SET SGA_TARGET=3G scope=spfile;
OR
ALTER SYSTEM SET MEMORY_TARGET=3G scope=spfile;

4. If you have pinned lots of packages with dbms_shared_pool.keep may be the reason of limit the SHARED POOL for other use.

--Check the pinned objects:
SELECT owner, name, kept FROM v$db_object_cache where kept = 'YES' and name = 'DBMS_ALERT';
no rows selected

--Use for KEEP the object:
execute dbms_shared_pool.keep('DBMS_ALERT');

--Check the keep object
SELECT owner, name, kept FROM v$db_object_cache where kept = 'YES' and name = 'DBMS_ALERT';

OWNER      NAME            KEP
---------- --------------- ---
SYS        DBMS_ALERT      YES
SYS        DBMS_ALERT      YES
--Use for UNKEEP the objects:
EXEC DBMS_SHARED_POOL.UNKEEP ('DBMS_ALERT');

--Check again
SELECT owner, name, kept FROM v$db_object_cache where kept = 'YES' and name = 'DBMS_ALERT';
no rows selected

5. You can also flush the shared pool for temporary fixed the issue.

alter system flush shared pool;