Re: data file compact

As indicated by David the script will shrink one of your datafiles back down to the high water mark. The high water mark of a tablespace is Oracles last allocated block of data inside that tablespace.

Allocated doesn't mean currently used as any available but pre-loved empty space will also be below your high water mark. For example if you created a table which was allocated blocks towards the end of your datafile, thus moving your high water mark, dropping this table does not move the high water mark back.

This script unfortunately will probably not work for you in the situation you previously described. If your datafiles are auto extending then the most you could hope to gain would be part of your last growth. i.e. if your datafile just grew by 5M because of a tables 1M extent allocation then you would gain back 4M.

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"ed zappulla" <zappullae_at_rcn.com> wrote in message news:9sr1qo$n1r$1_at_bob.news.rcn.net...
> fantastic! Thanks for the help.
>
> it looks a bit complicated. How does it work?
>
>
>
> "David LeJeune" <dlejeune_at_yahoo.com> wrote in message
> news:f329f8a7.0111121230.5af042fa_at_posting.google.com...
> > Hi -
> >
> > I always like having scripts. Hopefully the following script may
> > help. It will basically shrink the datafile to the high water mark of
> > the tablespace. Please make sure you have the autoextend feature of
> > your tablespaces on.
> >
> > =- Dave L.
> >
> > select 'alter database bdprod01 datafile ''' ||
> > a.file_name
> > || ''' resize ' || (b.maximum+c.blocks-1)*d.db_block_size
> > from dba_data_files a
> > ,(select file_id,max(block_id) maximum
> > from dba_extents
> > group by file_id) b
> > ,dba_extents c
> > ,(select value db_block_size
> > from v$parameter
> > where name='db_block_size') d
> > ,(select t.tablespace_name, sum(t.blocks) blocks
> > from sys.dba_tables t
> > group by t.tablespace_name) e
> > where a.file_id = b.file_id
> > and c.file_id = b.file_id
> > and c.block_id = b.maximum
> > and a.tablespace_name = e.tablespace_name(+)
> > order by a.tablespace_name,a.file_name
> >
> > "ed zappulla" <zappullae_at_rcn.com> wrote in message
> news:<9sm2pj$bq1$1_at_bob.news.rcn.net>...
> > > Thanks for the help. Same for the help from Sybrand.
> > >
> > > We do a nightly export so I can kill the table space and recreate to
> reclaim
> > > space.
> > >
> > > i'm not sure what you mean by, uniform extent size?
> > >
> > > I will defiitly check out the link.
> > >
> > >
> > > "Fraser McCallum" <fmcc_at_NOSPAModbaguru.com> wrote in message
> > > news:%HeH7.4648$ax1.49253_at_NewsReader...
> > > > 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 Tue Nov 13 2001 - 07:17:53 CST