Solution to ORA-04030: out of process memory

Today I encountered following error, while trying to run "Purge Logs and Closed System Alerts" to purge data from "FND_LOG_MESSAGES" table.

ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)

The values for the _PGA_MAX_SIZE and PGA_AGGREGATE_TARGET has been set to values even greater than the 4GB, but yet the same errors persist when 4GB is used.

The trace file also confirms that the process is limiting at 4GB.

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
4074 MB total:                                      
4072 MB commented, 192 KB permanent
1131 KB free (768 KB in empty extents),
4071 MB, 1 heap: "session heap "

These errors usually show up because of running out of map entries from the OS.
There are only 65536 memory map entries per process.

Solution

Change the upper limit at either the OS or at the database level:

• Change the page count at the OS level:

by root user,

$ more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=200000 (for example)

OR at database level,

• Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.

_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144

The default realfree allocator pagesize is 64 KB (65536) , so 64K entries take up 4GB. With 256KB (262144) pages, the limit goes to 16GB.