delete from large tables using loop+commit

-- delete_large_table.sql
set serveroutput on

-- this script will not use the index on where clause,
-- it will fullscan from the beginning, or add additional rowid
-- rownum does notw work well with index to filter large amount data
declare
-- commit every 'i_commit' rows
i_commit pls_integer := 1000;
-- row counter, 'i_rowcount' rows deleted
i_rowcount pls_integer := 0;

begin
-- define 'infinite' loop
  loop
  -- delete 'i_commit' rows
    delete from sec_owner.large_table
      where requesttime< '2010-10-01 01' and rownum <= i_commit;
      i_rowcount := i_rowcount + sql%rowcount;
    -- now it's time to exit the loop
    dbms_application_info.set_action(action_name => 'deleted '||i_rowcount||' rows');
    if sql%rowcount = 0 then
      commit;
      exit;
    end if;
    commit;
  end loop;
  -- feedback
  dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) || ' rows deleted') ;
  dbms_application_info.set_action(action_name => 'deleted '||i_rowcount||' rows (done)');
end;
/

SQL> select action from v$session where username='NCSDBA1' and action is not null;

ACTION
--------------------------------
deleted 130000 rows

SQL> select s.*, n.name
  2  from v$sesstat s ,v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name = 'user commits'
  5* and s.sid=513
SQL> /

       SID STATISTIC#      VALUE
---------- ---------- ----------
NAME
----------------------------------------------------------------
       513          4        130
user commits


SQL>