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
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.