Re: data file compact

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 - 05:59:29 CST