Untitled Document

Performing massive delete

When you are performing massive delete, you are always at risk of filling up UNDO tablespace. Also, if the delete statement fails for some reason, you have to start from the beginning again. The only way to track the progress is by looking at v$sess longops table, and its not very convinient. The short PLSQL block below will do the gradual delete of the rows, its faster the the one DELETE statement and its easy to track, if you set your SERVEROUTPUT ON, it wil print out the progress on the sceen. This block will COMMIT after every 10000 rows.

declare
   i_commit_int number:=0;
begin
   for i in (select rowid from MYTABLE) loop
     delete from MYTABLE where rowid=i.rowid;
     i_commit_int:=i_commit_int+1;
     if i_commit_int>=10000 then
        i_commit_int:=0;
        commit;
        dbms_output.put_line('10k marker');
     end if;
   end loop;
   commit;
end;
/