Re: Coalesce command for fragmentation

From: Jeremiah Wilton <jeremiah_at_wolfenet.com>
Date: Wed, 19 May 1999 16:21:41 -0700
Message-ID: <Pine.OSF.4.02.9905191621150.26306-100000@gonzo.wolfenet.com>

On Wed, 19 May 1999, Vick 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.

The ALTER TABLESPACE xxx COALESCE command only coalesces *contiguous* free space, which SMON does anyway if the default PCTFREE setting on your tablespaces is >0. COALESCE is different from a reorg because it just combines contiguous free space chunks.

20 or 30 extents is definitely not a bad thing, and you shouldn't reorg because of it. If you need to tune, start looking at v$sysstat and v$system_event to see where the time and resources are going.

HTH,
--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed May 19 1999 - 18:21:41 CDT