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 )