Re: deleting large number of records from table...

Bill,

One way to handle this is with PL/SQL or another language which can open cursors on Oracle. This allows you to use a loop counter to delete a smaller number of records (say, 10,000), then commit. By deleting in small chunks you don't overtax your rollback. Note that you have to open and close the cursor every trip through the loop to avoid a fetch-across-commits error.

There may be a way to handle it in SQL using rownum or some other limiting clause which would allow you to delete smaller groups. Maybe someone will offer an example of that.

Copying to another table and dropping the original is practical if you have the space. You could copy the rows you want to keep to a temporary table, truncate the original table (which generates no redo), and copy the temporary table back to the (now empty) original table.

Hope this helps,

Ken

bill wrote:
>
> Could anyone give advice for deleting a large number of records from a
> table. I am trying to remove about 1,500,000 records and I am running out
> of rollback space. Would it be better to create a duplicate table structure
> and copy the few records to it, then drop the first table?
>
> Thanks in advance for any help.
> Bill
Received on Mon Nov 10 1997 - 00:00:00 CST