Size of the Tablespaces Check

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;