Oracle Fragmentation
Is Fragmentation important?
Fragmentation can waste space. This could have a small percentage impact on full scan performance. If avoiding fragmentation is a priority for you, consider tuning PCTFREE for your segments. If performance is your priority, index properly. In cases where you have done a large delete resulting in wasted space, consider the online rebuild options available in 12.2...
ALTER TABLE mytable ENABLE ROW MOVEMENT;
Then any of these...
ALTER TABLE mytable SHRINK SPACE;
ALTER TABLE mytable SHRINK SPACE COMPACT;
ALTER TABLE mytable SHRINK SPACE CASCADE;
ALTER TABLE mytable MOVE ONLINE TABLESPACE users;
In previous versions the rebuild needs to happen offline... weigh up the benefits of saved space and a possible slight increase in performance versus a significant outage for large tables...
ALTER TABLE mytable MOVE TABLESPACE users UPDATE INDEXES;