To see free spaces in table spaces | Oracle DBA Tutorial pdf

To see free spaces in table spaces

SELECT b.tablespace_name, b."Total MB", ROUND(b."Total MB" -
a."Free MB",2) "Used MB",
a."Free MB", ROUND(((b."Total MB"- a."Free
MB")/b."Total MB")*100,2) "% used"
FROM (
(SELECT f.tablespace_name, ROUND(SUM(f.bytes/(1024*1024)),2)
"Free MB" FROM dba_free_space f
GROUP BY f.tablespace_name) a
RIGHT JOIN
(SELECT d.tablespace_name, ROUND(SUM(d.bytes/(1024*1024)),2)
"Total MB" FROM dba_data_files d
GROUP BY d.tablespace_name) b
ON a.tablespace_name = b.tablespace_name
)
ORDER BY 1