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; 

Comments