Purform Bulk Deletes on Tables with Commit for Every N number of Rows and update row count in Temp Table

1) Create Temporary Table for Logging the activity

create table snanabala.log_table (SEQ_NUM number(10), Time DATE , rows_deleted number(38));


2) Create Sequence for row sequence


create sequence snanabala.delete_sequence;

3) Now give grants on this table and sequence since we are using procedure to delete on table and logging in temp table

grant select,delete on snanabala.Sunil_archive to snanabala;  ( deletions on this table )
grant all on snanabala.log_table to snanabala;
grant all on snanabala.delete_sequence to snanabala;


4)   Now we need to perform deletions on sunil_archive Table .


CREATE OR replace PROCEDURE snanabala.Delete_rows_TEST1
AS

    ln_DelSize                      NUMBER := 1000;
    ln_DelCount                     NUMBER;

  BEGIN
    LOOP
           DELETE FROM SNANABALA.Sunil_ARCHIVE
             WHERE  
                  STARTDATETIME between to_date('10-01-2014 00:00:00','MM-DD-YYYY HH24:MI:SS') and to_date('10-01-2014 04:59:59','MM-DD-YYYY HH24:MI:SS')
              AND
                    rownum <= ln_DelSize;
ln_DelCount := SQL%ROWCOUNT;
                           dbms_output.put_line(ln_DelCount);
  INSERT INTO LOG_TABLE (SEQ_NUM, TIME, "ROWS_DELETED")  VALUES (delete_sequence.nextval,sysdate,ln_DelCount);
            EXIT WHEN ln_DelCount = 0;
            COMMIT;

   END LOOP;

END;
 /

5) Execute the above procedure

exec snanabala.Delete_rows_TEST1

6) Monitor how many deletions performing

select * from snanabala.log_table order by date desc