SELECT ROUND(MAX(d.BYTES) / 1024 / 1024, 2) "total MB",
DECODE(SUM(f.BYTES), NULL, 0, ROUND(SUM(f.BYTES) / 1024 / 1024, 2)) "Free MB",
d.file_name "Datafile name",
DECODE(SUM(f.BYTES),
NULL,
0,
ROUND((MAX(d.BYTES) / 1024 / 1024) -
(SUM(f.BYTES) / 1024 / 1024),
2)) "Used MB",
ROUND(MAX(d.BYTES) / 1024, 2) "total KB",
DECODE(SUM(f.BYTES), NULL, 0, ROUND(SUM(f.BYTES) / 1024, 2)) "Free KB",
DECODE(SUM(f.BYTES),
NULL,
0,
ROUND((MAX(d.BYTES) / 1024) - (SUM(f.BYTES) / 1024), 2)) "Used KB",
ROUND(SQRT(MAX(f.blocks) / SUM(f.blocks)) *
(100 / SQRT(SQRT(COUNT(f.blocks)))),
2) "Fragmentation Index"
FROM dba_free_space f, dba_data_files d
WHERE f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id
GROUP BY d.file_name