ORA-04030: out of process memory when trying to allocate 1536280 bytes (PLS non-lib hp,DARWIN)

Review of heap dump trace file Shows excessive growth of PGA components like "PLS non-lib hp" , "koh-kghu call" ( pmuccst: adt/re).

Cause:
Bulk insert in PLSQL can consume a large amount of PGA memory which can lead to ORA-4030 errors.
A heapdump will show lot of free memory in the free lists which is not used but instead fresh allocations are made.

if heapdump shows that "pl/sql vc2" "pmucalm coll" "pmuccst: adt/re" areas are majority of allocations, then it will be necessary to review applicable pl/sql code.


Workaround:
Chunk the FORALL loop. Do a hybrid of FOR & FORALL so that the bulk_rowcount arrays doesnt grow abnormally large.


Useful SQL for monitoring

To shows top PGA user..

Code:

select pid,spid,substr(username,1,20)
"USER" ,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,
PGA_MAX_MEM from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process where program not like '%LGWR%');