<markp7832_at_my-deja.com> wrote in message news:81u3oj$ir2$1_at_nnrp1.deja.com...
> In article <81tv5s$qnb$1_at_lure.pipex.net>,
> "Dave" <none_at_nowhere.nothere.oops> wrote:
> > I am running this SQL statment
> >
> > ALTER TABLE DAVE.ERMAIN
> > ADD CONSTRAINT ERRECNUM PRIMARY KEY
> >
> > RECNUM)
> > USING INDEX
> > TABLESPACE ERINDEX PCTFREE 5
> > STORAGE(INITIAL 10240 NEXT 10240 PCTINCREASE 0 )
> >
> > Every time I have tried I run out of Rollback Extents
> > in the System rollback or one of the production rollbacks.
> > Max is 121, I can't alter this as Oracle complains.
> > Why does an index creation need rollback?
> >
> > Any help
> > Dave
> >
> Dave, if Oracle will not let you change the max_extents value to
> greater than 121 then your database was probably built with a 2K Oracle
> block size. I would rebuild it with 8K or maybe 4K.
>
> If you are getting rbs extent errors on an index build I would look at
> recreating my rbs segments bigger.
> 1) Make sure you create them in a tablespace other than system
> 2) Set initial = next and minextents to at least 10. I would not use
> a minimum extents size less than 512K.
>
> You did not list the Oracle error message numbers, but I suspect that
> all your rbs segments are in the system tablespace as it seems to me
> that only a minimum amount of data is recorded in rbs for index
> builds. To the best of my knowledge the extents allocation change
> information has to be recorded but not much else. You also did not
> list your version of Oracle witch could come into play if a bug is
> involved.
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
I have Oracle 7.3.3 on Solaris 2.5.1
The Max Extents was caused by unlimited_rollback_segments not
being set to true.
I have an RBS table space which was there by default on installation
which I have altered to initial extent(I can't change) 128k next extent 20mb
It seems you can't alter the Max extents on rollback!!
I will point out that the table I have has 43,000,000 rows and the index
create takes about 28hrs on my Sparc Centre 2000. When it fails
after this amount of time it really fucks me off.
Received on Mon Nov 29 1999 - 09:56:51 CST
