There might be severe problems if the bulk number of rows are deleted fromt the production table. As this will lock the table in shared mode. And resource of the server will be used, offcourse it will use the UNDO tablespace heavily.
Following is the procedure which can be used to delete the bulk rows in batches on certain conditions. Also it will run in loops which can be controlled and also once it will delete the records it will put the message into purge_history table.
declare
type table_rowid_Array is table of rowid index by binary_integer;
l_rids table_rowid_Array;
v_counter number := 0;
v_counter_total number := 0;
v_id_counter number := 0;
begin
for i in 1 .. 500
loop
v_id_counter := i;
select rowid
bulk collect into l_rids
from BIG_LOG_TABLE where log_date < '1-JUN-2008' and rownum < 25001;
for i in 1 .. l_rids.count
loop
delete from BIG_LOG_TABLE where rowid = l_rids(i);
v_counter := v_counter +1 ;
end loop;
dbms_output.put_line( v_counter ' rows deleted' );
v_counter_total := v_counter_total + v_counter;
v_counter := 0;
insert into purge_history values (v_id_counter,v_counter_total,sysdate,BIG_LOG_TABLE );
commit;
end loop;
dbms_output.put_line( v_counter_total ' total rows deleted' );
end;
