ORA-04030 out of memory process when dealing with 8-million-element arrays
I need your advice as my knowledge as a DBA is quite poor. I'm facing an ORA-04030 error when running a simple PL/SQL block involving associative arrays. In short, here is what the block does:
- BULK COLLECT a 8 million-record table into an associative array.
- Looping through this associative array to feed another associative array index by varchar2(32).
Both these array are tables records. I created a track table to find out when the memory problem occurs. It does after having processed about 1 million elements.
The database runs on a 64 Gb Linux server, so I guess this should be enough to deal with this much data. I suspect then a parameter setting problem, but I don't really know what the correct values should be. From the server side, the max memory allocated is 60 Gb, from the database, here is what the parameters with 'target' show: