Hello Experts,
I am getting the ORA-04030 error while trying to execute anonymous block.
ORA-04030: out of process memory when trying to allocate 16328 bytes
(koh-kghu sessi,pl/sql cursor meta-data)
ORA-06500: PL/SQL: storage
error
ORA-04030: out of process memory when trying to allocate 16328 bytes
(koh-kghu sessi,pmucalm coll)
The anonymous block has logic to select records from a table (SRC_TABLE) and insert them into another table (DEST_TABLE).
The few columns in select statement on the SRC_TABLE has user defined functions applied on them, below is pseudo of anonymous block.
Declare
...
Begin
exit_flg := 'N';
min_rng := 1;
max_rng := 50000;
Loop
Exit When exit_flg = 'Y';
exit_flg = 'Y';
Open l_refcur For Select ... From SRC_TABLE ... Where rownum between min_rng and max_rng;
Loop
Fetch l_refcur
Bulk Collect INTO l_tab LIMIT 1000;
FORALL IN l_tab
Insert Into Dest_Table Using l_tab;
Commit;
Exit When l_tab.Count < 1000;
l_tab.Delete;
End Loop;
min_rng := min_rng + 50000;
max_rng := max_rng + 50000;
End Loop;
End;
The SRC_TABLE has more than 5 million records. The block executes fine till 2.5 million records after which I get ORA-04030 error at Fetch statement in block above.
I understand that the issue is due to lack of memory and since there is no more memory available on my server I am trying to alter the block to execute it successfully. To so do I have introduced another loop upon refcursor and for each iteration made sure only 50000 records are fetched and inserted at a time but still the same error is thrown.
Can someone help me understand why my fix is not working. If memory is not sufficient then why is it working fine till 2.5 million records are processed?
Imran.