SELECT df.tablespace_name,
df.autoextensible,
df.allocated_mbytes,
df.maximum_mbytes,
nvl(fs.free_mbytes,0) free_mbytes,
df.allocated_mbytes - nvl(fs.free_mbytes,0) used_mbytes,
CASE autoextensible
WHEN 'YES' THEN ROUND((df.allocated_mbytes - nvl(fs.free_mbytes,0)) / maximum_mbytes * 100)
WHEN 'NO' THEN ROUND((df.allocated_mbytes - nvl(fs.free_mbytes,0)) / allocated_mbytes * 100)
END percent_used,
ROUND((df.allocated_mbytes - nvl(fs.free_mbytes,0)) / allocated_mbytes * 100) alloc_used
FROM ( SELECT tablespace_name,
ROUND (SUM(bytes) / 1024 / 1024, 2) allocated_mbytes,
autoextensible,
ROUND (SUM(maxbytes) / 1024 / 1024, 2) maximum_mbytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name, autoextensible
) df,
( SELECT tablespace_name,
ROUND (SUM(bytes) / 1024 / 1024, 2) free_mbytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name
) fs
WHERE df.tablespace_name = fs.tablespace_name (+)
ORDER BY tablespace_name;