Re: data file compact

From: David LeJeune <dlejeune_at_yahoo.com>
Date: 12 Nov 2001 12:30:06 -0800
Message-ID: <f329f8a7.0111121230.5af042fa@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 Mon Nov 12 2001 - 14:30:06 CST