% Of Using Oracle Tablespaces: Allocation in MB, Use in MB Free in MB, Use in% in% Free
To view the utilization of table space in%, run the SQL below:
SELECT a.TABLESPACE_NAME “Tablespace Name”
round (a.BYTES / 1024/1024) “MB Allocated”
round ((a.BYTES-lvl (b.BYTES, 0)) / 1024/1024) “Used MB”
nvl (round (b.BYTES / 1024/1024), 0) “MB Free”
round (((a.BYTES-lvl (b.BYTES, 0)) / a.BYTES) * 100.2) “% Used”
round ((1 – ((a.BYTES-lvl (b.BYTES, 0)) / a.BYTES)) * 100.2) “% Free”
FROM (SELECT tablespace_name,
sum (BYTES) BYTES
DBA_DATA_FILES FROM
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
sum (BYTES) BYTES
sys.dba_free_space FROM
GROUP BY tablespace_name) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES) /a.BYTES);