Re: No of extents of Free space making up the tablespace too high

I think you are trying to coalesce free extents:

alter tablespace <tablespace_name> coalesce;

This finds adjacent free extents in the tablespace and makes them into one, larger extent - for actually putting data in to segments which might otherwise be too large. The reason an export/ import doesn't help is that empty extents aren't automatically coalesced by Oracle unless the default pctincrease for the tablespace is non-0.

Kingsley

Anurag Minocha wrote in message <37984BDF.CD1D05DE_at_synergy-infotech.com>...
>Hi,
>
>I am using the following query
>
>select tablespace_name, sum(bytes), max(bytes), count(tablespace_name)
> 2 from dba_free_space
> 3 group by tablespace_name
> 4 order by tablespace_name;
>
>I get the following results when i run the above query.
>
>TABLESPACE_NAME SUM(BYTES) MAX(BYTES)
>COUNT(TABLESPACE_NAME)
>------------------------------ ---------- ----------
>----------------------
>INDX 3227648 1884160
>42
>USR 252651520 102574080
>48
>
>The results of this query tell how much free space is
> available within a tablespace (sum), what the size of the largest
>contiguous extent size is (max), and how many extents of free space
>make up the tablespace (count). If the number of contiguous blocks is
> greater than 10 to 15, you should defragment the tablespace(Can someone
>tell me why )
>
>.Defragmanting can be done by exporting and importing the tables.
>I have tried that but it still doesnt work.
>
>Any help will be appreciated
>
>Thanks
>anurag
>
>reply at
>anurag_at_synergy-infotech.com
>
>
Received on Fri Jul 23 1999 - 09:29:39 CDT