Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: avoiding tablespace fragmentation

Re: avoiding tablespace fragmentation

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 7 Jun 2004 05:55:00 +1000
Message-ID: <40c37681$0$1586$afc38c87@news.optusnet.com.au>

"yls177" <yls177_at_hotmail.com> wrote in message news:c06e4d68.0406060426.2ab5ccf0_at_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 Received on Sun Jun 06 2004 - 14:55:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US