Re: compress/compact database?

THere is no harm.
Tables are broken into extents, extents are stored sequentially (if you wish).
If you have multiple schemas your datafiles might like like this;

File1
<user_1_table_1_extent_1>
<user2_table_2_extent_1><user_1_table_1_extent_2>....

u get the idea

On Thu, 2 May 2002 07:13:01 -0400, "Winbatch" <winbatch_at_techie.com> wrote:

>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
>> >
>> >
>>
>>
>
>

.......
We use Oracle 8.1.7.3 on Solaris 2.7 boxes remove NSPAM to email Received on Thu May 02 2002 - 16:56:11 CDT