Re: Rollback Segment Errors

The most efficient way to perform "mass deletes" is to truncate. If you have to delete all the records, it's very efficient and straightforward. If you need to keep only a portion of the existing records in a table, the way I would try to handle it is by disabling/dropping the indexes, copying the records to be kept in a temporary table (and I DON'T mean temporary in the Oracle sense, but in the conceptual sense), truncate or drop the initial table, and finally recopy the records from the "temporary" to the initial table. Use "create table ... as select ... where ..." to move records around. Truncate the initial table if there is something worth keeping (grants, triggers, procedures referencing it, extents allocated to it, constraints, ...), otherwise just drop it. Don't forget to rebuild the indexes and constraints (if applicable) afterwards.

Daniel

> Is their anyway to perform mass deletes (several million records) without
> "maxing out" rollback segments?
>
> I'm working on archiving data from an Oracle 8.1.7 database The system is
> about 4 years old and no data has EVER been archived /removed. So the
> two largest tables contain well over 25 million records!
>
> As part of the archive process I'm deleting out-dated records, but because
> of the sheer volume keep receiving "Unable to extend Rollback Segment
> errors." I've tried to commit every 50 deletes, I've even tried committing
> after
> every single delete, but eventually always receive this error. Our DBA
> does not want to extend the rollback segments (probably with good reason).
>
> This process will run off-hours, so there's no conflict with end-users.
>
> TIA for any insights you can provide.
> Tomas
Received on Fri Jun 27 2003 - 15:41:06 CEST