Update or Delete a large table in a loop and commit very X rows

Summary 

It is very usual in large updates or delete to have rollback segment problems. To avoid the rollback you can use this handy script which commits every 10.000 records.

declare
  i number := 0;
  cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1';
begin
  for c1 in s1 loop
      update tab1 set col1 = 'value2'
             where rowid = c1.rowid;

      i := i + 1;              -- Commit after every X records
      if i > 10000 then
         commit;
         i := 0;
      end if;

  end loop;
  commit;
end;
/