TS BADLY FRAGMENTED

[BRAUK]

I had tables and indexes in same tablespace.I used:
alter index index_name rebuild tablesspace index_ts ;
to put all the index in separate ts.
Now I find that my user tablespace(where I had tables and indexes) is badly fragmented(FSFI <<20).
How do I get out of this?

[mallard]

You can unfragment your indexes by rebuilding the index. You can use the following command:

ALTER INDEX index_name
REBUILD;

then other options can also be used, such as, TABLESPACE to move the index to another tablespace, PCTFREE to change the pctfree, etc.

[rcaballe]

You can do an
ALTER TABLE MOVE ONLINE
With parameters like strorage or tablespace.
But spaces between extents are not bad.

[mallard]

I'm sorry....I re-read your post and see that it is your tablespace that is fragmented, not your indexes. You can take care of this by coalescing the tablespace.

ALTER TABLESPACE tablespace_name COALESCE;

If pctincrease is set >0 for the tablespace, it should coalesce automatically. If set to 0 then you will need to manually coalesce using the above command.

[BRAUK]

THANKS GUYS!