Re: data file compact

Of course there is
alter tablespace <tablespace_name> coalesce which will merge all adjacent free extents into one.
Once an extent is allocated it will never be deallocated whether it is empty or not. The only way to release allocated extents is to truncate the object. When a new extent is allocated Oracle will simply try to find an free extent which is big enough to fit the allocated extent in. It looks like your tablespaces are so badly fragmented, you will get the tablespace extended for any new extent.
Generally speaking one should try to avoid - fine grained extent allocation especially when pctincrease is non-zero. This almost always results in wildly fragmented free space - dictionary managed tablespace when on 8i or higher. Dictionary managed tablespace are supported in 9i for backwards compatibility only.

So in your case, it looks like you need to _reorganize_ your tablespaces, as coalesce will definitely not deal with honeycomb fragmentation. The only way to do this, when you don't have the bucks for Platinum TsReorg, is ... export/import

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address



"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

> >

> >

> >

> >

>

>