BULK COLLECT FAILS WITH ORA-04030

Recently We have a procedure that reads and distract data from a larger table O_APACHE_LOG(about 40 million rows per day) to many smaller one under some condition for ETL.

[@more@]

We are using the BULK COLLECT、FORALL and INSERT ALL statements to process data.When we have run the procedure,we capture two exceptions(ORA-04030: 在嘗試分配 16408 位元組 (koh-kghu call ,pmucalm coll) 時程式記憶體不足 & ORA-04030).we know that these exceptions occured because Operating system process private memory has been exhausted.

Please be aware that BULK operations (using FORALL etc.) just need a lot private (PGA) memory.This is not a bug or we don't need to immediately think of a memory leak.

Still it is recommended to apply the latest patchset as usually some memory leaks get fixed in a patchset and it is clear any memory you can save can be used for the bulk operations.
This is a clear sign that you have insufficient memory to execute the BULK operations which is also clear from the above error message.

As per the recommendation give by the Expert (ARE) the Potential solutions are,

- increase swap on the Linux system
- add more physical RAM
- lower the sga size (so pga will get more room)
- make sure only one such high pga demanding process runs at the same time.(i.e)execute the procedure when there is no or very less activity in the Database.
- break up the bulk collect into pieces on the application side, especially when there's no bound on the potential number of records that must be processed.