When a large number of records neds to be deleted from a table. If you do a normal delete it will consume the UNDO tablespace. To avoid such a scenario in a production environment. You can use below procedure to commit frequently. In my opinion it is better to schedule the procedure as a daily job.
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, name of table and other criteria
delete from obj
where rownum <= i_commit and dd_date < sysdate-3;
i_rowcount := i_rowcount + sql%rowcount;
— now it’s time to exit the loop
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’) ;
end;
/
