#801

declare 
e number;
i number
begin
  select count(*) from <TABLE_NAME> where <CONDITION>;
  f :=trunc(e/10000)+1;
  for i in 1.. f
  loop
    delete from <TABLE_NAME> where <CONDITION> and rownum<=10000;
    commit;
    dbms_lock.sleep(600); -- purge old archive if it's possible
  end loop;
end;

DECLARE
 v_limit PLS_INTEGER :=100000;

CURSOR delete_cursor
IS 
  SELECT rowid 
  FROM <TABLE_NAME> p
  WHERE <CONDITION>;

TYPE recordsToDeleteType IS TABLE OF delete_cursor%ROWTYPE
        INDEX BY PLS_INTEGER;
		
recordsToDelete recordsToDeleteType;

BEGIN
  OPEN delete_cursor;
    LOOP
      FETCH delete_cursor 
        BULK COLLECT INTO recordsToDelete LIMIT v_limit;

    FORALL indx IN 1 .. recordsToDelete.COUNT 
      DELETE FROM <TABLE_NAME> WHERE rowid=recordsToDelete(indx);

    EXIT WHEN delete_cursor%NOTFOUND;

   END LOOP;

   CLOSE delete_cursor;
  COMMIT;
END;

BEGIN
LOOP

delete FROM <TABLE_NAME> 
  where <CONDITION> and rownum < 50000;

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

database, oracle