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

I got ” ORA-04031: unable to allocate nn bytes of shared memory ” error in Oracle.

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

Details of error are as follows.

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

Cause: More shared memory is needed than was allocated in the 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".

unable to allocate nn bytes of shared memory

This ORA-04031 errors are related with the Low setting for streams_pool_size and shared_pool size.

SQL> alter system flush shared_pool;

SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 137573171
shared_pool_size big integer 2624M
shared_server_sessions integer
shared_servers integer 1
SQL> 
SQL> 
SQL> show parameter stream

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL> 
SQL> 
SQL> 


SQL> alter system set streams_pool_size=4G scope=spfile sid='*';

System altered.

SQL> alter system set shared_pool_size=8G scope=spfile sid='*';

System altered.

SQL>

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )