Xref: alice comp.databases.oracle.server:39528 Path: alice!news-feed.fnsi.net!enews.sgi.com!harbinger.cc.monash.edu.au!news.bhp.com.au!atbhp.corpmel.bhp.com.au!news From: Connor McDonald Newsgroups: comp.databases.oracle.server Subject: Re: Maxextents = 2 billion? Date: Tue, 02 Feb 1999 17:46:02 +0800 Organization: Broken Hill Proprietary Company Limited. Lines: 64 Message-ID: <36B6C95A.5E9B@bhp.com.au> References: <36B14FFA.A3933EB7@accesscom.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Mailer: Mozilla 3.01 (Win95; I) To: Van Messner 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@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 > > > > Maxextents used to be dependent on block size - different block sizes gave the 121, 249 etc limits you are refering to... From 7.3 you have "maxextents unlimited" as an option...although 2 billion might be considered a little high (by about 1.99999 billion) Cheers -- ============================================== Connor McDonald BHP Information Technology Perth, Western Australia "Never wrestle a pig - you both get dirty and the pig likes it..."