Reducing database fragmentation

Extremely fragmented tables or indexes can harmfully affect performance. By using the following SQL statement you can identify those database objects that have over 10 extents allocated:

select * from dba_segments where extents > 10;

And if a table or index has more than 10 extents then rebuild it to fit into one extent. A table can only be rebuilt by import/export utility. But to do it we need a downtime from application team because database will be unavailable. We can proceed with the below mentioned steps to complete the activity successfully.

1. Export the table with argument COMPRESS=Y
2. Drop the table
3. Import the table.

The entire index can be rebuilt without preventing others from still using it.

Change the storage parameters to make the ‘next’ storage parameter larger (possibly double it). The initial storage value cannot be changed and later on rebuild the index

Please correct me if I am wrong, Friends.........