Re: Fail to extend rollback segment ?
Date: 1996/11/04
Message-ID: <327DD0FB.21DC_at_drecon.cummins.com>#1/1
Kaboel Karso wrote:
>
> 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.
> > --
> > ===================================================
> > http://www.li.net/~gsa/index.html
> > This is my signature file, not part of this mail message.
>
> 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.
The error you are getting does NOT say that you have ran out of space in the datafiles, it tells you that you can not obtain another extent because you have reached maxextents. Depending on the settings for your inital and next extents, the object could reach maxextents at 1 meg or 500 meg or whatever size. The object needs more space (another extent) but is prohibited from obtaining that space by the maxextents parameter. Basically, the object is allowed 121 extents and no more.
121 extents is the max for a database with a 2K block size. What you can try to do is to export the data from the table, drop the table and then recreate the table with a larger initial extent and set the next extent parameter to also be larger. You can also use the pctincrease parameter, but be sure to read about it first. Be sure you have a good backup prior to this tasks.
Good luck.
-- Mike Cuppett, Oracle DBA Voice 901.546.5689 Fax 901.546.5627 Cummins Engine Aftermarket 4155 Quest Way, Memphis, TN 38115Received on Mon Nov 04 1996 - 00:00:00 CET