Re: Alter tablespace xxxx coalesce -- when does it happen ???

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/11/12
Message-ID: <34694efd.19303513@netnews.worldnet.att.net>#1/1

It happens when your tablespace has "honeycomb"-type fragmentation, i.e. the tablespace contains several CONTIGUOUS FREE SPACE extents.

Try this "quick-and-dirty" query to see the fragmentation in a tablespace:

SELECT file_id, block_id, segment_name, blocks FROM dba_extents
WHERE tablespace_name='YOUR_TABLESPACE'
UNION
SELECT file_id, block_id, "FREE SPACE", blocks FROM dba_free_space
WHERE tablespace_name='YOUR_TABLESPACE'
ORDER BY 1, 2; Any two or more consecutive rows returned by the above query with "FREE SPACE" and all having the SAME file_id, represent extents that should be coalesced by the ALTER TABLESPACE .... COALESCE command.

Hope this helps.

Michael Serbanescu

---

On 11 Nov 97 17:41:23 GMT, austby_at_centuryinter.net (A Ustby) wrote:

>Hi,
>
>I just tried a "alter tablespace xxxxx coalesce;". When does the coalesce
>actually happen ? Because it didn't seem to happen.
>
>Thanks for any help.... Art
>
Received on Wed Nov 12 1997 - 00:00:00 CST