How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP ...do some stuff... COMMIT; END LOOP; COMMIT;
... to ...
FOR records IN my_cursor LOOP ...do some stuff... i := i+1; IF mod(i, 10000) = 0 THEN -- Commit every 10000 records COMMIT; END IF; END LOOP; COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.