Re: Rebuild table
From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Wed, 22 Jun 2011 09:55:23 +0200
Message-ID: <4E019FEB.2070303_at_gmail.com>
On 22/06/2011 09:52, Radoulov, Dimitre wrote:
> On 22/06/2011 03:47, Michael Rosenblum wrote:
>> Extra 2c. I would suggest as a possible solution already mentioned
>> ALTER TABLE ... MOVE, but fired twice:
>> - first, move everything to completely different tablespace
>> - second, move it back where the data belongs
>> - rebuild indexes
>> To be fair, I don't exactly remember the real case, but sometimes ago
>> (Oracle 10g R2 on Win32) this double-jump solved my space allocation
>> problem, while single operation was not not giving me what I wanted.
>
> Yes,
> this happens when the default initial/next (for dmt) or uniform (for
> lmt) size is big.
> Bear in mind that in that case although the space allocated could be
> large,
> the HWM is reset (and a full table scan, for example, actually reads
> only up to the HWM).
>
... and in that case you could reclaim some space with "deallocate unused [keep 0]".
Date: Wed, 22 Jun 2011 09:55:23 +0200
Message-ID: <4E019FEB.2070303_at_gmail.com>
On 22/06/2011 09:52, Radoulov, Dimitre wrote:
> On 22/06/2011 03:47, Michael Rosenblum wrote:
>> Extra 2c. I would suggest as a possible solution already mentioned
>> ALTER TABLE ... MOVE, but fired twice:
>> - first, move everything to completely different tablespace
>> - second, move it back where the data belongs
>> - rebuild indexes
>> To be fair, I don't exactly remember the real case, but sometimes ago
>> (Oracle 10g R2 on Win32) this double-jump solved my space allocation
>> problem, while single operation was not not giving me what I wanted.
>
> Yes,
> this happens when the default initial/next (for dmt) or uniform (for
> lmt) size is big.
> Bear in mind that in that case although the space allocated could be
> large,
> the HWM is reset (and a full table scan, for example, actually reads
> only up to the HWM).
>
... and in that case you could reclaim some space with "deallocate unused [keep 0]".
Dimitre
Dimitre
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 22 2011 - 02:55:23 CDT