Re: Primary key Index Problem

In article <81u7se$4dt$1_at_lure.pipex.net>,   "Dave" <none_at_nowhere.nothere.oops> wrote:
>
> <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.
>

Dave, if you want to change the extent size of your rbs segments you can alter an rbs segment offline, then drop and re-create it using different extent sizes. Normally you can alter the max-extents setting of a rollback segment. If the segment already has more extents than you want to set it to you probably need to set the optimal down to the size less than what you want to allow and use the 'alter rollback segment x shrink;' command to give up the extra extents first.

With an unlimited extent setting the only space error I would think that you could encounter is that there is not enough space in the rbs tablespace to hold the next rbs extent. Maybe you need to add afile to the rbs tablespace.

I do not know anything about the sparc center 2000, but I can build a primary key (1G) on a 40 million row table (3G) in under 1 hour. But box size and disk layout have a huge impact in this area. We do our maintenance with the instance in restricted mode. We reset our sort area size to 40M and set our temp tablespace default storage to initial and next of 50M whenever we do maintenance. This really helps performance on the big indexes. Perhaps you can make a couple of similiar of changes that will help you.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 30 1999 - 10:17:52 CST