Database Object Fragmentation in Oracle

If a table or index becomes fragmented (not continuous on disk) performance can decrease. You can see which objects have over 10 extents with:

select * from dba_segments where extents > 10;

If it is more than 10 it is a candidate for rebuilding.

To rebuild a table:

- Export the table (with compress=Y)
- Drop the table
- Import the table

To rebuild an index:

- Use Flex2SQL to drop and recreate it. Possibly hand crafting the 'next' storage parameter

Recommendation: Inform customers to check their tables that have possible performance and if they do, recreate them.