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

ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

Action: first we need to check shared pool size using below command.

Show parameter shared_;

sho parameters sga_target

select * from ( select POOL, NAME, BYTES, BYTES/1048576 as MBytes from v$sgastat where pool='shared pool' order by BYTES desc ) where rownum <= 25;

select * from V$LIBRARY_CACHE_MEMORY;

try to do flushing of shared_pool and try to restart the system.. it seems your SGA defragmented

====================================

SQL> alter system flush shared_pool;

Action: 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 INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

1 ps -ef|grep oracle

2 find the smon and kill the pid for it

3 SQL> startup mount

ORACLE instance started.

Total System Global Area 4831838208 bytes Fixed Size 2027320 bytes Variable Size 4764729544 bytes Database Buffers 50331648 bytes Redo Buffers 14749696 bytes Database mounted. SQL>

4 SQL> alter system set shared_pool_size=100M scope=spfile;

System altered.

5 SQL> shutdown immediate

ORA-01109: database not open

Database dismounted. ORACLE instance shut down.

6 SQL> startup

ORACLE instance started.

7 SQL> create pfile from spfile;

File created.