================================================== ===========
set term on
prom List of Fragmented Tables (Size more than 1 GB)
set term off
prom ================================================== ===========

SELECT UT.OWNER "OWNER" , UT.TABLE_NAME "TABLE NAME",
ROUND(UT.NUM_ROWS*UT.AVG_ROW_LEN/1024/1024,2) "CALCULATED SIZE MB",
ROUND(US.BYTES/1024/1024,2) "ALLOCATED SIZE MB",
ROUND(US.BYTES/(UT.NUM_ROWS*UT.AVG_ROW_LEN),2) "TIMES LARGER"
FROM DBA_TABLES UT, DBA_SEGMENTS US
WHERE (UT.NUM_ROWS > 0 AND UT.AVG_ROW_LEN > 0 AND US.BYTES > 0)
AND UT.PARTITIONED = 'NO'
AND UT.IOT_TYPE IS NULL
AND UT.IOT_NAME IS NULL
AND UT.TABLE_NAME = US.SEGMENT_NAME
AND ROUND(US.BYTES/1024/1024,2) > 1024
AND ROUND(US.BYTES/1024/1024,2) >
(ROUND(UT.NUM_ROWS*UT.AVG_ROW_LEN/1024/1024,2)* 2) ORDER BY 4 DESC;