Re: defragmenting free space in a tablespace

Steve Preisach <steve_at_mailhost.gate.net> wrote:

>> >How does one condense all the free spaces in a tablespace into one big
>> >contiguous free space?
>>
>> I believe you have to do an EXPort, drop the tables (making note of
>> your grants first), then do an IMPort. This should clean up
>> everything.
>>
>> - Steve O'Hearn
 

>Oracle V7 takes care of this itself. For V6, creating a dummy tables of
>the appropriate sizes to occupy the free space will force the database to
>re-combine _adjacent_ free spaces. I have a Pro*C program that will
>accomplish this for all available tablespaces, if you desire. Contact me
>via E-mail for a MIME or UUE copy. I use this tool every time I drop any
>tables. It has greatly increased the time between exports for me.
 

>Steve.

But I *am* using Oracle7... 7.1.4 on Netware to be exact. It doesn't appear to be doing anything about automatically reorganizing the tablespace. Because of time constraints, I ended up adding datafiles to the tablespace making it nearly twice as large as it needs to be, but 50% of the space is wasted in small, non-contiguous free spaces.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!