How to handle ORA-04030 issues

Issue Case: During the performing of data dump, we got a message of  ORA-04031/ORA-04030  issues.

    作为一般的原则，页面文件中的空闲容量总量应该不低于总容量的一半。交换文件应该几乎不使用，空闲的容量应该几乎和总容量一样。

1.Windows系统：在任务管理器中查看内存使用情况。

2.Unix系统：每一个Unix系统都有自己的工具来检测全部内存的使用情况，例如top,vmstat…..，并且每一个系统都有所不同。

o        top常用来显示物理内存和交换空间的情况。

o        swapon　–s　显示交换空间使用情况

o        vmstat　显示空闲物理内存情况

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

1) Oracle solution: 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)

2) OS solution: 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