ALTER TABLESPACE <tablespace_name> COALESCE

Hi everybody,

Command
ALTER TABLESPACE <tablespace_name> COALESCE; can be issued for a tablespace to coalesce all contiguous free extents into larger extents.

This command coalesces only free space and doesn't touch extents with data. So, if we have fragmentation like:

free extent
extent with data
free extent

then "ALTER TABLESPACE <tablespace_name> COALESCE;" won't help us a lot.

So, the command above helps only with contiguous free extents. Will speak further only about such contiguous free extents:

I thought we need to run "ALTER TABLESPACE <tablespace_name> COALESCE;"to prevent the case when all free extents are smaller in size than a NEXT extent parameter for an object(/segment) from this tablespace. Say, we have a table in a tablespace and when this table will need to get a new extent (of its NEXT extent size), then we may get a problem if all available free extents are smaller in size.

But then I found in documentation ("Oracle 8i Concepts"): "In dictionary-managed tablespaces, when a segment requires an extent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. This is called coalescing extents. "

So, why we ever need "ALTER TABLESPACE <tablespace_name> COALESCE;" command at all ?!?!?!

Am I right in my analysis ? Where is my mistake ?

note (jus for info): we have pctincrease parameter = 0, so no any automatic coalescing.

Thanks lot for the help,
Tanya. Received on Thu Mar 14 2002 - 11:55:52 CST