Re: compress/compact database?

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 - 00:02:44 CDT