Object Fragmentation in Oracle

SELECT a.owner,a.table_name,round(SUM ((b.BYTES)/1024/1024),0) occupied_mb,round((a.num_rows*a.avg_row_len/1024/1024),0) "Actual_Used(MB)",

SUM ((b.BYTES)/1024/1024) - round((a.num_rows*a.avg_row_len/1024/1024),0) Wasted, a.PARTITIONED,a.LAST_ANALYZED,a.TABLESPACE_NAME

FROM dba_tables a,dba_segments b

WHERE a.table_name = b.segment_name and a.owner='ABC' and round((b.bytes/1024/1024),0) > 100

group by a.owner,a.table_name, a.num_rows,a.avg_row_len,a.PARTITIONED,a.LAST_ANALYZED,a.TABLESPACE_NAME order by 6,3 desc;

 http://jonathanlewis.wordpress.com/2013/06/19/wasted-space/ andhttp://jonathanlewis.wordpress.com/2012/08/27/fragmentation/. Fragmentation is a topic that's frequently mentioned (and thoroughly discussed) in Jonathan Lewis' Scratchpad: http://jonathanlewis.wordpress.com/category/oracle/infrastructure/fragmentation/.