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.

You can flush the shared pool to solve this error.
SQL> alter system flush shared_pool;
To solve this error, Increase the streams_pool_size and shared_pool_size of the database from the current size and restart the database to resolve the issue.
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>
Now Restart database to see if the problem is solved or not.
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 )

 

 584 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply