Delete large number of records Oracle

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;
/

Category: Database

Tags:

Leave a Reply

Article by: Shadab Mohammad