Re: how to reduce file size to minimum?

I thought alter tablespace coalesce merged adjacent free blocks into contiguous space, used for example for tables where pctincrease = 0 and this is not done automatically. rather than creating free space out of partially used blocks.

I don't see at all how it can be used to defragment your datafiles, but then I am still using primarily 7.3.4. Change will come.

I'd be interested to know if this does work in 8+

Geoff
Geoff Reader
Analyst Programmer
University of Brighton

Patrick Greville-Morris wrote:

> 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.
>
> Have fun
>
> Patrick Greville-Morris
> Consultant DBA
>
> +49 (0)69 4980025 (Home)
> +49 (0)1736647428 (Mobile)
>
> "Norbert Lemke" <ofenroor_at_gmx.de> wrote in message
> news:9k5ubg$2rft1$1_at_ID-76747.news.dfncis.de...
> > Hi,
> >
> > I need to reduce the free space in my database-files, therefore I want to
> > minimize the filesize. To do this I have to reorganize the segments that
 all
> > segments are located at the beginning of the datafiles.
> >
> > How can I do this (except export all the database)?
> >
> > Regards,
> > Norbert
> >
> >
  Received on Thu Aug 02 2001 - 05:48:03 CDT