How To Reclaim Wasted Space In Tables

There are several ways to reclaim wasted space in a table but they work differently.

Create table As Select (CTAS)
This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space.

Online re-organization
Using the dbms_redefinition package you can use parallel CTAS to reorganize tables, while the tables continue to accept updates.

Data pump (expdp, impdp)
Rarely used in high-speed production systems, except for backups.

Alter table move
The alter table xxx move command moves rows down into the un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.

Alter table shrink space
Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

SQL> alter table mytable enable row movement;

SQL> alter table mytable shrink space;