Re: compress/compact database?

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 - 06:13:01 CDT