Re: data file compact

Ed,

Try Alter tablespace xxxx coalesce;

This will do the defragmentation of free space, what this means is that if there are two segments of free space next to each other it will make them one larger segment. However it does not shrink datafile nor does it rearrange the contents of the tablespace to compact the data.

You can shrink a datafile (look up alter datafile) if and only if there is no data at the tail end of it.

I suspect your real problem is a lack of uniform extent size. The best space allocation comes when you choose table and index sizes using uniform extent sizes. Rather than re hashing what's been said well else where suggest you look at http://www.ixora.com.au/tips/creation/extents.htm for a good explanation of what I'm talking about.

Ultimately I would suggest, and this is my personal preference, a reorganisation of your database into locally managed tablespaces with uniformed extent sizes.

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"ed zappulla" <zappullae_at_rcn.com> wrote in message news:9sjqj3$oks$1_at_bob.news.rcn.net...
> Compacting refers to the process where by holes in data files are
> deframented and the data file shrunk to release unused space.
>
> We do have the table spaces on auto extend. purposfully. our thought was
> that it was needed to allow for growth.
>
> Dose Oracle not reuse table space if auto extend is on, extend before
> reusing freed space?
>
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:tuqhjc537ah764_at_corp.supernews.com...
> >
> > "ed zappulla" <zappullae_at_rcn.com> wrote in message
> > news:9sjedu$c8j$1_at_bob.news.rcn.net...
> > > Is there a way to compact the data files that oracle 8.1.6 uses? They
> > seem
> > > to be growing very large while the amount of data an indexs held
remains
> > the
> > > same.
> > >
> > >
> > >
> >
> > There is no mechanism to 'compact' the datafiles, whatever you may mean
by
> > compacting.
> > Please check whether you have any tablespace(s) on autoextend. This must
> be
> > the case otherwise datafiles wouldn't grow. Please also check how much
of
> > the allocated space is actually occupied.
> > If you don't know how to do that please read the Oracle Server
> > Administrators Manual.
> >
> > Hth
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
> >
> > to reply remove '-verwijderdit' from my e-mail address
> >
> >
> >
> >
>
>
Received on Sat Nov 10 2001 - 12:57:28 CST