Upgrading Oracle errors out with ORA-04031: unable to allocate ## bytes of shared memory (“shared pool”

While working to get my Oracle database upgraded to a supported version, using the DBUA I was getting the following errors:

ORA-04031: unable to allocate 704 bytes of shared memory 
("shared pool","unknown object","PX subheap","qref (kxfpqr)")

or in a different upgrade

ORA-04031: unable to allocate 40 bytes of shared memory 
("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp")

Unable to find anything helpful in “My Oracle Support” (Metalink) or in a Google search, I opened a ticket with Oracle support.  After two weeks and uploading many log & trace files,  Oracle support final got someone who came back with the following recommendation:

 

To resolve this;

1) set the sga_target to a large value and this will fix the issue

OR

2) Reduce the PARALLEL_MAX_SERVERS and CPU_COUNT to a lower value

 

The SGA_TARGET was set larger then many databases that I have already successfully upgraded.

However, my PARALLEL_MAX_SERVERS was set to 1,200.  When I reduced that to 200, the upgrade completed successfully.   Also on the “Upgrade Options” tab, I left the “Select Upgrade Parallelism” at the default of 4.  Before adjusting the PARALLEL_MAX_SERVERS parameter, the higher I set the Upgrade Parallelism, the faster I filled up the shared pool.

Leave a Reply