Table Fragmentation in oracle

select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',

((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name =’ table_Name’ AND OWNER LIKE 'schema_name';

 

 

PAYSOUTH_REG    PAY_EARNINGS    18944kb    15808.48kb    3135.52kb    6.55152027027027027027027027027027027027

 

 

Gather table states:

 

exec dbms_stats.gather_table_stats('schema_name','table_name');

 

 

Rebuild all indexes:

 

select index_name from user_indexes where table_name like 'table_name';