Re: Rollback Segment Errors

"tkestell" <tkestell_at_attbi.com> wrote in message news:<lSPKa.28822$3d.17163_at_sccrnsc02>...
> 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

Tomas, if you are commiting as you go you should not be getting an 'unable to extend' error unless your rbs tablespace is badly fragmented and some segments have no room to grow at all, or the maximum size of your rbs segmens is less than the amount of data you are trying to purge.

May I suggest you first verify that your rbs tablespace/segments do not require manual intervention to clean them up. I would suggest running multiple purges with each purge going after the oldest data such as data < 3.75 yrs, then 3.5, then 3.25 or similar where the amount of data in each pass will fit in the maximum size of a rollback segment.

You may want to allocate a special large rbs segment just to support this process. The following article may be of interest:

How do I associate an active session with a rollback segment ? http://www.jlcomp.demon.co.uk/faq/active_rollback.html

HTH -- Mark D Powell -- Received on Fri Jun 27 2003 - 16:18:47 CEST