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;
Smart way of Technology 