Reclaim waste space in a table

To find out total space wasted in a table
========================================

--exec as the owner of the table/sys

Select table_name,tablespace_name,status,num_rows,chain_cnt,
blocks,empty_blocks,avg_row_len,
(blocks * 8192 )/1024/1024 "Used_Size_MB",
round(((num_rows * avg_row_len)/1024/1024),2) "Acutal_Data_Size_MB",
((blocks * 8192 )/1024/1024) - (round(((num_rows * avg_row_len)/1024/1024),2)) "Space_Wasted_MB"
FROM   user_tables
ORDER  BY 11 DESC;



Methods to reclaim wasted space in a table
==========================================

1. Alter table table_name move tablespace users; 

 --Move table to any other tablespace
 --Then back to the original tablespace
 --Note that this operation will change rowids of the rows
   because of this indexes on that table will go invalid, we need to rebuild all of them manually;
 --DML,DDL cannot be done during this time.


2. Create table as select * ...

   --This will create a table similar to the original one, But constraints and indexes should be taken care.

3. alter table table_name shrink space;
 
 --execute 'alter table table_name enable row movement;'
 --execute 'alter table table_name shrink space compact;'(This will rearrange the rows, this doest not required dml locks)
 --execute 'alter table table_name shrink space;'(actual shrinking operation, requires locks,DML cannot perform during the time)
 --Indexes remains valid
 --gather stats on the table 'exec dbms_stats.gather_table_stats(TAB_OWNER, 'MYTABLE', cascade => TRUE);'

4. DBMS_REDEFINITION  

 --Online redefinition (does not required any down time for the table)
 --create an interim table as per the requirement
 --check for the eligibility to do redefinition using DBMS_REDEFINITION.can_redef_table
 --start redefinition by using  DBMS_REDEFINITION.start_redef_table
 --Sync table with interim table by using DBMS_REDEFINITION.sync_interim_table
 --Copy dependent objects (such as triggers, indexes, grants, and constraints)
   using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 --finfish redefinition  using DBMS_REDEFINITION.finish_redef_table


For more detail please visit:
http://avdeo.com/2012/10/28/redefining-tables-online-oracle-11g/