ORA-04030 with bulk collect: memory workarounds
Two workarounds for ORA-04030 during bulk collect on Linux: max_map_count and the realfree heap.
The error ORA-04030 ("out of process memory when trying to allocate") can occur with bulk collect operations on Linux, when the process runs out of memory mappings or of heap memory. Two workarounds are documented.
Workaround 1: raise vm.max_map_count at the OS level
Check the current value:
more /proc/sys/vm/max_map_count
Set it to 200000 for the running system:
sysctl -w vm.max_map_count=200000
To keep the value after a reboot, add the parameter to /etc/sysctl.conf and reload it.
Workaround 2: change the realfree heap page size
Set these hidden parameters in the init or SPFILE and restart the database:
_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144
Hidden parameters (those starting with an underscore) are not documented and must only be changed on the advice of Oracle Support. See the related article on hidden parameters.
Notes
- The workarounds come from an article on bulk collect and memory limits: https://www.performatune.com/en/bulk-collect-and-memory-limits/
- For huge pages and the memory setup of Oracle on Linux, see https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64