. How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?

Contrary to popular believe, 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 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;

... to ...

            FOR records IN my_cursor LOOP

               ...do some stuff...

               i := i+1;

               IF mod(i, 10000) THEN    -- Commit every 10000 records

                  COMMIT;

               END IF;

            END LOOP;

If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.