SELECT
a.tablespace_name "TABLESPACE_NAME",
ROUND(a.current_mb) "CURRENT SIZE(MB)",
ROUND(a.max_mb) "MAXIMUM SIZE (MB)",
ROUND(a.max_mb - (a.current_mb - c.free)) "FREE SPACE (MB)",
ROUND(a.current_mb - c.free) "USED_MB",
ROUND(((a.current_mb - c.free) * 100) / a.max_mb) "USED PCT%"
FROM
(
SELECT
tablespace_name,
SUM(a.bytes) / (1024 * 1024) current_mb,
SUM(
DECODE(
a.autoextensible,
'NO',
a.bytes / (1024 * 1024),
GREATEST(
a.maxbytes / (1024 * 1024),
a.bytes / (1024 * 1024)
)
)
) max_mb
FROM dba_data_files a
GROUP BY tablespace_name
) a,
(
SELECT
d.tablespace_name,
SUM(NVL(c.bytes / (1024 * 1024), 0)) free
FROM dba_tablespaces d,
dba_free_space c
WHERE d.tablespace_name = c.tablespace_name(+)
GROUP BY d.tablespace_name
) c
WHERE
a.tablespace_name = c.tablespace_name
ORDER BY
ROUND(((a.current_mb - c.free) * 100) / a.max_mb) DESC;
```html
```