Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: compress/compact database?

Re: compress/compact database?

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Fri, 3 May 2002 08:03:00 +1000
Message-ID: <aasd3u$fh1$1@lust.ihug.co.nz>


No, there's no harm in trying to resize a file, even if it fails.

But this reverse chronological bit is wrong, generally. Extents of a segment are acquired when needed (usually), so you can't say that the oldest table is at the front of the file, and the youngest at the end. The oldest table's *first* extent is probably going to be at the front of the file, sure enough. But if it needs to grow, it will acquire extents from wherever else in the file there is free space enough to accommodate them... and that will almost certainly be from a place located well after tables which are created later. So extents end up scattered all through the datafile, and deleting schemas in reverse chronological order is likely just to leave gaps of empty space scattered through the file.

Regards
HJR "Winbatch" <winbatch_at_techie.com> wrote in message news:aar717$k5b$1_at_bob.news.rcn.net...
> HJR,
> Thanks for the detailed response. Could you explain this particular part
> further? Does this mean in effect is that data is stored sequentially,
and
> I can only really save space easily if the schemas I delete are at the end
> of the datafile? (Meaning I would have to delete schemas in reverse
> chronological order?) Also, is there any harm in attempting to do the
> resize command even if it fails?
>
> Winbatch
>
> >The command you ultimately want to use that does much the same sort of
> thing
> > is 'alter database datafile 'path/filename' resize Xm; ... where X is
> > whatever size you want to shrink the file down to.
> >
> > The trouble is, if there is any data encountered on the way 'down' to
size
> > X, the command will fail. So what you need to do first is make sure that
> > your data is compacted nicely at the front of the datafile; that way,
the
> > resize can lop off chunks at the end of the file without a problem
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:aaqhb0$krg$1_at_lust.ihug.co.nz...
> > Are you thinking of something like the 'shrink database' option in SQL
> > Server? If so, there is no one-command equivalent.
> >
> > The command you ultimately want to use that does much the same sort of
> thing
> > is 'alter database datafile 'path/filename' resize Xm; ... where X is
> > whatever size you want to shrink the file down to.
> >
> > The trouble is, if there is any data encountered on the way 'down' to
size
> > X, the command will fail. So what you need to do first is make sure that
> > your data is compacted nicely at the front of the datafile; that way,
the
> > resize can lop off chunks at the end of the file without a problem.
> >
> > So: to compact you data, the best bet is probably to query dba_segments
to
> > find out what tables etc. are stored in the tablespace concerned. Then
you
> > run export in table-mode, and list each and every one of those segments.
> > That produces an export dump file containing your valuable data. Return
to
> > the database, and drop all those tables. Run import, and have all the
> tables
> > re-created and re-populated with data. Being freshly created like this,
> the
> > tables will congregate at the front of the datafile. When import's
> finished,
> > resize the datafile.
> >
> > And all of that answers your last question: it's a very expensive bit of
> > reorganization. From the time of the 'drop table' statements until the
> > import has finished, the data is unavailable. Effectively, that's
> downtime.
> > That much hassle and downtime for the saving of a few gigabytes of disk
> > space is probably not worth the effort.... which might seem a bit of a
> > cavalier approach, but Oracle is a high-end database, and disk space is
a
> > relatively cheap commodity (particularly compared to DBA time, and
> > downtime).
> >
> > Unless we were talking about recovering tens of gigabytes, I wouldn't
> > bother.
> >
> > Regards
> > HJR
> >
> >
> > "Winbatch" <winbatch_at_techie.com> wrote in message
> > news:aaqejf$dvq$1_at_bob.news.rcn.net...
> > > Hi,
> > > I was wondering if there is a command that I can use to compress a
> > database.
> > > We have deleted a number of large schemas recently and was wondering
if
> > > there would be any disk space savings by running some sort of compress
> on
> > > the database. Is this even necessary in Oracle?
> > >
> > > Thanks,
> > > Winbatch
> > >
> > >
> >
> >
>
>
Received on Thu May 02 2002 - 17:03:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US