ORA-04030: out of process memory Tips

Oracle Database Tips by Donald Burleson

Question: On an auto execute of an external job I get the error "ORA-04030: out of process memory when trying to allocate nn bytes".

Answer: This ORA-04030 can be caused by a shortage of RAM on a dedicated (non shared server) environment, a too small PGA, and by not setting kernel parameters large enough to allow enough RAM. The ORA-04030 is also common when running an import. See import ORA-04030 error tips.

Also see MOSC Note 233869.1 titled "Diagnosing and Resolving ORA-4030 errors".

Essentially the ORA-04030 is clear, and "out of process memory" error, whereby Oracle cannot get the RAM needed to complete the operations. Depending on your release of Oracle, you have several options:

Increase pga_aggregate_target
Decrease sort_area_size and/or hash_area_size
Move to multi-threaded server (a.k.a. MTS or shared servers)

Kernel Parms - (Karpinski, Joseph E) In installing Oracle 10G we ran into a number of ORA-4030 issues.

We changed the setting of "ulimit" as follows to resolve the issue:

ulimit –a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194302
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 4096

It's also part of the Oracle 10G pre-install checklist:

Section "ULimits OK?"

For more on tuning sorting, see these pages:

��