-- =======================================================================
--	This SQL script identifies tables in an Oracle database with significant space wastage by comparing the physical size of table segments with the actual amount of data stored.
-- =======================================================================
SELECT
  a.owner,
  a.segment_name,
  a.segment_type,
  round(a.bytes / 1024 / 1024, 0) MBS,
  round((a.bytes - (b.num_rows*b.avg_row_len) ) / 1024 / 1024, 0) WASTED 
FROM
  dba_segments a,
  dba_tables b 
WHERE
  a.owner = b.owner 
  AND a.owner NOT LIKE 'SYS%' 
  AND a.segment_name = b.table_name 
  AND a.segment_type = 'TABLE' 
GROUP BY
  a.owner,
  a.segment_name,
  a.segment_type,
  round(a.bytes / 1024 / 1024, 0),
  round((a.bytes - (b.num_rows*b.avg_row_len) ) / 1024 / 1024, 0) 
HAVING
  round(bytes / 1024 / 1024, 0) > 100 
ORDER BY
  round(bytes / 1024 / 1024, 0) DESC 
 /