Table Fragmentation

To find if there is fragmentation in a table run the following script -

select table_name,round((blocks*8/1024),2) "TABLE SIZE (MB)",round((num_rows*avg_row_len/1024/1024),2) "ACTUAL DATA (MB)",
round((blocks*8/1024),2)-round((num_rows*avg_row_len/1024/1024),2) "DIFF (MB)"
from dba_tables
where OWNER ='SCOTT'
ORDER BY 4;

If you find few tables where the colum "DIFF" is too high then you may need to reset the High Water Mark or remove fragmentation. We can achieve by taking one of the following step -

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition