Re: Coalesce command for fragmentation

Vickie,

The coalesce statement will only coalesce all contiguous free extents into a larger contiguous extent for each data file in the tablespace. This will not defrag your tablespace.

I find that it is best to backup and export your tablespaces, drop them, delete the datafiles at the OS level and recreate your tablespaces and import.

20 to 30 extents for a table is not excessive. You could use the compress option on your export and it will create an initial extent that will fit all your data. You will have to do your homework and figure out if you really want a large initial extent since there are many variables at the OS level and the size of your data files come into play. You also need to know some history of the application population the tablespace so you can come up with a valid next extent.

Any other question you can email me.

David Knollhoff

In article <7hv5p6$ii5$1_at_nnrp1.deja.com>,   Vick <vramming_at_my-dejanews.com> wrote:
> I read on Ari Kaplan's web page that you can use
> ALTER TABLESPACE xxx COALESCE
> /
> (where xxx is the appropriate tablespace_name)
>
> to defragment tablespaces instead of exporting, dropping and
> importing. And that this can be done "on the fly" with users
connected.
>
> I inherited a 3 year old DB and it has never been tuned. It runs, but
> the tables have many extents in the 20's and 30's. This is extremely
> bad from what I have read so far.
>
> Help would be great or, a re-direct to the source for further research
> material would also be appreciated.
> Thanks very much,
> Vickie
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 19 1999 - 18:02:40 CDT