Re: Free space and extents [ora8i]

On Tue, 14 Feb 2006 21:14:31 +0100, Morff <morff1_at_wp.pl> wrote:

>Dnia 14-02-2006 o 21:01:00 Sybrand Bakker <postbus_at_sybrandb.demon.nl>
>napisa?:
>
>> On Tue, 14 Feb 2006 20:22:58 +0100, Morff <morff1_at_wp.pl> wrote:
>>
>> ora-1652 means there is no <num> blocks *contiguous* free space
>> available.
>> You might be using
>> - dictionary managed tablespaces ----> get rid of them and convert to
>> locally managed
>> -- pctincrease > 0 on your index definition ----> get rid of it on all
>> of your indexes, this is the #1 source for ora-1652.
>>
>> for *dictionary managed* tablespaces (obsolete, thing of the past),
>> Oracle will coalesce automatically if pctincrease on tablespace level
>> is >0. But then pctincrease is just plain EVIL.
>> Locally managed tablespaces don't need to be coalesced ever.
>>
>> --
>> Sybrand Bakker, Senior Oracle DBA
>
>Thanks for explain .. but how (besides locally managed) obtain contiguous
>free space in this tablespace ?
>I can rebuild all indexes with new storage options (I will check to
>pctincrease <> 0 .. in weekend :) . But I think this is not solution for
>now .
>Is there another solution than create another file for this tablespace ?

alter tablespace <tablespace_name> coalesce

And likely the rebuild will free space.

--
Sybrand Bakker, Senior Oracle DBA