Title: Avoiding ORA-1555 Error When Deleting Large Tables

Author: Ujwal Shrivatsa, a Technical Consultant for Database Consultants, Inc. in San
Antonio, Texas.

Deleting large number of rows from a table requires a large enough rollback segment. Where rollback segments are luxury, you
might get the error, ora-1555 "snapshot too old: rollback segment number %s with name \"%s\" too small."

To avoid this problem, the procedure described below commits after every N number of rows specified by the user so that
rollback segments are not taxed.

-- Ujwal Shrivatsa  September 1998

--This procedure deletes rows from the table specified in tname for the
--condition in where_clause. It commits after every n records specified by the parameter commit_size .
-- eg : execute delete_table('emp','where job=''clerk''',2000);
-- if where_clause and commit_size is not specified the procedure deletes
-- all the records for the table specified commiting after every 1000 rows


CREATE OR REPLACE PROCEDURE delete_table
                                         (tname in varchar2,
                                          where_clause in varchar2 default null,
                                          commit_size in number default 1000) is

  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;
  cur_stmt    VARCHAR2(2000);
  del_rowid   ROWID;
BEGIN
  --Prepare cursor to select rowids of the records to be deleted.
  cur_stmt := 'SELECT rowid FROM '||tname||' '||where_clause;
  sel_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sel_id,cur_stmt,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);
  --Prepare cursor to delete records
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete '||tname||
               ' where rowid = :del_rowid',dbms_sql.v7);
 LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN
      --Get the rowid to be deleted
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      --Execute the delete for the rowid got
      exec_del := DBMS_SQL.EXECUTE(del_id);
      -- Commit for every n records where n is specified in commit_size
      if mod(dbms_sql.last_row_count,commit_size) = 0 then
      COMMIT;
      end if;
   ELSE exit;
   END IF;
 END LOOP;
 -- Commit again to catch the last set of records and close the cursors
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(sel_id);
  DBMS_SQL.CLOSE_CURSOR(del_id);
END;
/