PL/SQL trick: delete rows in tranches.

delete-tranches.sql

--
--  delete rows in tranches
--
--  inspired by the discussion at http://kr.forums.oracle.com/forums/thread.jspa?threadID=365130
--

set serveroutput on

DECLARE
  ln_DelSize      NUMBER := 500;
  ln_DelCount     NUMBER;
  ln_Total        NUMBER := 0;
BEGIN
  LOOP
    DELETE FROM table WHERE rownum <= ln_DelSize;
    ln_DelCount := SQL%ROWCOUNT;
    ln_Total := ln_Total + ln_DelCount;
    EXIT WHEN ln_DelCount = 0;
    COMMIT;
  END LOOP;

  dbms_output.put_line('deleted '||ln_Total||' rows');
END;
/