Re: When should I use COMPRESS=Y on export?

Hi Russ,

I think your approach to compress=y is a bit too cautious. Certainly, if you just use it without thinking, you will indeed increase the risk of fragmentation. But if you use it on 'mature' segments (ie, ones which have grown to a number of extents, but now have little liklihood of acquiring a next extent), there won't be a problem. In those cases, compress=y is a valuable 'fix-it-up' tool.

Given that we should all be using locally managed tablespaces anyway, however, the discussion becomes a little moot!! ;-)

Incidentally, I never make do with just a single "Users" tablespace. In your case, I'd have two: one with inital and next of 1Mb and one with initial and next of 10Mb. The point being: Initial should invariably be the same as next. You still have odd-sized extents in your tablespace.

Take care,
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------




"Russ Marcil" <rmarcil_at_ucsd.edu> wrote in message

news:3A109018.883D954A_at_ucsd.edu...



"Howard J. Rogers" wrote:

You are correct in saying that using compress=y might mean the thing is
totally unimportable -in that last example, if I need to create a single 10M
extent, and I have only got 3M in one part of the tablespace, 4M in another,
and a final 3M at the end, then I won't be able to create a single 10M
extent, and the import will fail.  That's what tablespace fragmentation is
all about, and if you plan your tablespaces properly, and use consistent
extent sizes, it won't happen.  Failure to import is, in other words, a
symptom of a much deeper problem, not an issue with using COMPRESS=Y per se.
Howard,
My feeling is that extensive use of compression for exports/imports will
result in lots of difference sized extent sizes within the "user tables"
tablespace... and will lead to fragmentation as you described above.  I size
all my small tables with initial extent of 1MB, all my large tables with
initial extent of 10MB, and all tables with next extent size of 1MB.