Shrink table space

SQL> create table waste_table as select * from dba_objects;

Table created.

SQL> set lines 2000
SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
6291456 WASTE_TABLE

SQL> delete from waste_table;

50553 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
6291456 WASTE_TABLE

--to avoid table lock if end users are accessing it

SQL> alter table waste_table shrink space compact;
alter table waste_table shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table waste_table enable row movement;

Table altered.

SQL> alter table waste_table shrink space compact;

Table altered.

SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
6291456 WASTE_TABLE

--issue the command 2nd time, to lower down the High Water Mark
SQL> alter table waste_table shrink space ;

Table altered.

SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
65536 WASTE_TABLE