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

If you have pctincrease <> 0 on your tablespace, this is automatic and doing it manually serves no purpose.

Michael Serbanescu wrote:
>
> 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
> >
 

-- 
Gary Melhaff
Senior Database Analyst
Weyerhauser Corporation