Re: Fail to extend rollback segment ?

Gary Assa wrote:
>
> >>when I insert 200,000 records into a table from a query, I encounter
> >>the following errors.
 

> >>ORA-01562: failed to extend rollback segment (id = 1)
> >>ORA-01628: max # extents (121) reached for rollback segment RB_TEMP
 

> >>I have added a datafile size 20M (total is 35M)
> >>and increase the initial to 1M and next to 500K on ROLLBACK_DATA
>
> 500k is a ridiculously small NEXT for such a big INSERT. I'd make an initial
> of 10M and a NEXT of 5M, this way you get over 500M before you run out
> of extents.
> --
> ===================================================

hmmm,
the fastest way is of course to create the datafiles as big as necessary to accomodate the growth of your RBS. Diskspace is cheap these days. But wouldn't it be a waste if this will be the only time you need to run such a big transaction ?. Or, create a temporary rbs with associated datafile(s), enough to run this HUGE transaction and drop it if you don't need it anymore. Or, write a PL/SQL script to break up your transaction. Received on Mon Nov 04 1996 - 00:00:00 CET