Re: How to "Defragment" Tablespaces ?

Carl <c_joh_at_hotmail.com> writes:

> Hello!
>
> You need to export the tablespace (with compress = Y), drop it
> recreate the tablespace and import it.

Careful with that ! Compress = Y rewrites the CREATE TABLE statements in the dump so they have an initial extent that is large enough to hold all data of the table. This is not always desirable.

Consider the following situation: You have a table with 100 extents of 10 Megs that lived in 4 Datafiles each 256 Megs. This is a perfectly sound situation. You export with compress=Y and import again. This will give you a table with an initial extent of 1 Gig. You will not be able to create this table, because your datafiles are only a quarter gig. You need to adjust the sizing of your datafiles too. Also you will not have equally sized extents anymore.

IMHO the idea of having just ONE extent for an entire table is pretty much a myth. Having 20,000 extents IS bad though. But A few hundred is absolutely okay. Received on Sat Apr 07 2001 - 16:40:11 CDT