SQL & PL/SQL

Hi everyone,

I have to to remove a large amount of double entries from a huge table based on three different criteria.

The affected database is running 24/7 with a lot of concurrent access including many DML commands. There is no possibility to lock the DB while the cleanup script is running.

So far I've used a PL/SQL script which is doing mainly the following steps:

1. Get a list of Ids (both primary key id and internal ID) via BULK collect into a collection (May contain upto 1.500.000 records to remove)
2. Pass the retrieved data to FORALL to actually remove these values
3. Finally commit

Tests on our Test environment shows that the runtime might easily exceed 6-8 hours (the affected table is really large) per Script. So my collegues asked me to perform a commit every 2000 records.

I'm not sure if FORALL utilises the SQL*Plus feature "SET AUTOCOMMIT ON 2000" ?

If this is not the case (which I assume), is there any other way to foce FORALL to commit on every 'N' records? Or do I have to change the code to use the BULK COLLECT LIMIT clause and feed FORALL with that "batch"?

Hope that someone could give some hints on that topic.

Thanks for help!

Best regards,

Sascha