Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!sjc1.usenetserver.com!news.usenetserver.com!border1.nntp.sjc.giganews.com!border2.nntp.sjc.giganews.com!nntp.giganews.com!news1.optus.net.au!optus!newsfeeder.syd.optusnet.com.au!news.optusnet.com.au!not-for-mail From: "Howard J. Rogers" Newsgroups: comp.databases.oracle.server References: Subject: Re: avoiding tablespace fragmentation Date: Mon, 7 Jun 2004 05:55:00 +1000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Lines: 41 Message-ID: <40c37681$0$1586$afc38c87@news.optusnet.com.au> NNTP-Posting-Host: 203.164.5.21 X-Trace: 1086551682 1586 203.164.5.21 Xref: core-easynews comp.databases.oracle.server:222274 X-Received-Date: Sun, 06 Jun 2004 12:54:02 MST (news.easynews.com) "yls177" wrote in message news:c06e4d68.0406060426.2ab5ccf0@posting.google.com... > is it true that as long as i dont drop/truncate tables, then there > will be no tablespace fragmentation provided that i set my storage > parameters at tablespace level as the below > > 1) initial extent=next extent > 2) pctincrease 0 > > thanks I think I have answered this at great length elsewhere in replies to you. Starting a new thread on the same topic isn't going to change the answer. Tablespace fragmentation is what happens when (A) you drop or truncate or move objects, thus freeing space; and (B) where the objects so moved, truncated or dropped had different sizes of extents. Your points 1 and 2 do NOT guarantee the same sized extents, because they are merely default storage clause items, and default storage clauses can be ignored by segments that are created with their own storage clauses.Therefore, your points 1 and 2 cannot guarantee no tablespace fragmentation, because (B) above could still happen. If you promise completely and utterly 100% that you will never, ever, move, drop or truncate an object in a tablespace, then fragmentation will not happen, because you are not doing (A) above. But the chances of you truly being able to keep such a promise for the lifetime of a database is slim. As I am sure I've told you before, a better way to avoid tablespace fragmentation is to use the MINIMUM EXTENT clause. And the only 100% cast-iron way of never getting fragmentation is to use uniformly-sized locally managed tablespace. Why are you asking all of this all over again? Regards HJR