Alter table move vs. shrink space commands

Question:  What is the difference between "alter table move" and shrink space?

Answer:  There are several ways to reclaim wasted space in a table, but they work differently.  Also, see my notes on identifying tables for space reclamation.

• 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 reorg - 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 apps, except for backups.
   
• Alter table move - The alter table xxx move command moves rows down into 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;
Table altered

SQL> alter table mytable shrink space;
Table altered

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.