Thanks for the question, DIPU.
Asked: August 04, 2016 - 3:36 pm UTC
Last updated: August 06, 2016 - 1:43 pm UTC
Version: 10 g
Viewed 1000+ times
You Asked
I have a table with 1 Million records. Because of undo log size, i can not delete this 1M records in one shot. So, need to create a PLSQL block which will delete the record in batches (let's say undo log can sustain .1 M records at one shot). Could you please tell me how i can efficiently write a PLSQL block to achieve this?
and Connor said...
Easiest way - get a larger undo tablespace - even if you simply create one temporarily.
But if you cannot...then you need a means of identifying the rows to delete, so that you can run the delete repeatedly.
For example, let's say the criteria was "status = 'OLD'". You could do:
begin
loop
delete T where status = 'OLD' and rownum < 100000;
exit when sql%notfound;
commit;
end loop;
end;