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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s