Re: Free space and extents [ora8i]

On Tue, 14 Feb 2006 20:22:58 +0100, Morff <morff1_at_wp.pl> wrote:

>Helo
>
>I have a tablespace for indexes . When I count a free space the result is
>3,5 gb in all files (there is some files for this tablespace) but in alert
>log I have an information : ORA-1652: unable to extend temp segment by
>14243 in tablespace T_INDEX . Is it mean that there is no space to
>allocate extent like that ? . Dba_free_space for this tablespace shows the
>biggest extent with size 70mb - should I coalesce ? (I thought oracle
>cares about that) ..
>
>thanks for solutions ..

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