Re: Maxextents = 2 billion?

The maxextents is not platform dependent. It is either release dependent or block size dependent. If you have Oracle 7.3 or above, it is possible to have the unlimited maxextents value you are seeing. With release 7.2 and prior releases, if you had a block size of 2K your maxextents was 121; 4K maxextents is 249; and so on.

I am not sure what would have caused a crash while trying to rebuild an index which had had a maxextents of unlimited. It could be as Jonathan suggested. Or it could just be that there were so many extents to rebuild that Oracle just threw up.

Terry Ball
Sr. DBA, CSG Systems

Van Messner wrote:

> Hello Andy:
>
> Thanks to you and the others who answered. I thought that maxextents
> had a limit
> which was platform dependedent - 121 or 249 or whatever. Is this not true?
> If it is true
> how does Oracle treat a limit of 2 billion?
> In terms of crashing the server. Each time I tried to rebuild an index
> with one of the
> enormous limits, it crashed. Every other index rebuilt without any
> problems - none of them
> had maxextents set larger than 249.
>
> Van
>
> Andy Johnson wrote in message <36B14FFA.A3933EB7_at_accesscom.net>...
> >Van Messner wrote:
> >
> >> I started work at a site with ten out of tune instances. I was
> looking
> >> for objects which had thrown extents and found more than a thousand with
> >> over 50 extents each.
> >> As I started to look at the worst of them I discovered ten had
> >> maxextents set to 2.nnnE+09. I was surprised that Oracle accepted a
> value
> >> this high. Can anyone explain why this is allowed?
> >> Trying to rebuild these indexes resulted in a server crash for each
> >> attempt. I ended up dropping them and making new ones.
> >> My guess is that someone was entering storage parameters for
> maxextents
> >> = 249 and hit the "e" key which is just below the "4" key.
> >>
> >> Thanks for any info
> >>
> >> Van
> >
> >When a table is created with maxextents unlimited, the number is actually
> set
> >to 2^31, so, no, it's not a mistake, just bad practice. I usually set mine
> to
> >200, so at least if something breaks and starts using space like crazy it
> >won't use all of it before I can fix it. I have some automated apps (third
> >party) that do this once a blue moon (actually, the next blue moon is this
> >weekend, so I have a lot to look forward to).
> >
> >Andy Johnson
> >
> >
Received on Mon Feb 01 1999 - 11:30:10 CST