Re: how to reduce file size to minimum?

Hi,

"Patrick Greville-Morris" <patrick.greville-morris_at_t-online.de> wrote in message news:<9k65mh$6qi$00$1_at_news.t-online.com>...
> This can be done with the following statement
>
> ALTER TABLESPACE tablespacename COALESCE;
>
> This has the effect of compacting the data stored in the datafile to free up
> the partially used blocks into a large set of contiguous blocks.
> Then you can reduce the datafile (.dbf) size until it is close to 100% full
> WARNING!!! You should start the database in restricted mode to do this to
> ensure that there are no locks or procedures running.
>

That's not right. The coalesce will compact free extents, which following in order into one extent (i.e. 2 free extents of 2MB , the second follows the first on physically, will be after coalescing one free extent of 4MB) Bute coalescing is not defragmenting. If you have used extents in the end of your datafile (even if there are only free extents in the beginning), you only can resize until this point in datafile . For a effective resizing you really have to export/import or table move to other tablespaces, then coalescing old tablespace and move back.

Hth
 Thomas Received on Fri Aug 03 2001 - 03:41:59 CDT