Re: Rollback Segment Errors

Daniel,
The more people I talk with the more it sounds like the majority of DBA types consider your solution the better and most efficient one.

Thanks for your insight ... I'll pursue your suggestions. Tomas

"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message news:3722db.0306270541.7b855ab9_at_posting.google.com...
> 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 Sat Jun 28 2003 - 04:42:31 CEST