problem with fragmentation

[manjunathk]

Hi friends,
Some of my tablespaces are very badly fragmented. I did 'alter tablespace ts_name coalese', but this seems to be making only a small improvement.
I had table partitions and their index partitions on these tables. When the structure needed to be changed, I dropeed the table, took offline the datafiles, dropped the tablespaces and then removed the datafiles at os level.
I again created symbolic links between raw partitions and the datafiles and then recreated the entire tablespace/tables/indexes with the new structure. But, for some reason the newly created tablespaces are all fragmented. Does anyone know why and help to get over with this fragmentation?
my INITIAL and NEXT are sized equally and are 12M
Thanks
manjunath

[pando]

what do you mean fragmentated? The number of extents? Fragmantation is about free spaces in the tablespace not really the number of extents

[manjunathk]

Thanks Pando,
I also meant the tablespace fragmentation.
Consider the following:
SQLWKS> select tablespace_name,
2> sqrt(max(blocks)/sum(blocks))* (100 /sqrt(sqrt(count(blocks)))) FSFI
3> from dba_free_space
4> group by tablespace_name
5> order by 1;
TABLESPACE_NAME FSFI
------------------------------ ----------
ICDR_IDX_TS_1 1.95682422
ICDR_IDX_TS_2 70.3302453
ICDR_IDX_TS_3 70.3302453
ICDR_TS_1 2.29933999
ICDR_TS_2 29.9994489
ICDR_TS_3 29.9994489
INDX 100
RBS1_TS 80.5096526
REFERENCES_TS 100
SYSTEM 100
TEMP .349922129
TOOLS 100
USERS 84.0373916

Dont you agree that something needs to be done about ICDR_IDX_TS_1 and ICDR_TS_1 ? Coalescing these tablespaces is not working .
Thanks
manjunath

[pando]

well you have to reorganize your indexes in those two tablespaces, if they are indexes you can rebild them to another tablespace, if they are tables try alter table move tablespace xxxx

[ales]

Few weeks ago I had similar problem.
If you have the right version of Oracle you don't need rebuild indexes to another tablespace.
I used ALTER INDEX iii REBUILD ONLINE and it helped much.
I started from the smallest index and after rebuilding each I called ALTER TABLESPACE ts COALESCE although I wasn't sure it was useful.
Indexes have shrunk to 50-60% their original size and I got great performance benefit.

Ales