Free space in Tablespaces

SELECT a.tablespace_name TABLESPACE,
            a.total_space TOTAL_SPACE,
            b.free_space FREE_SPACE,
            a.total_space_mb TOTAL_SPACE_MB,
           (a.total_space_mb - b.free_space_mb) USED_SPACE_MB,
            b.free_space_mb FREE_SPACE_MB,
            ROUND(100 * (b.free_space / a.total_space),2) PCT_FREE
FROM
           (SELECT tablespace_name,
                        SUM(bytes) TOTAL_SPACE,
                        ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
            FROM    dba_data_files
             GROUP BY
             tablespace_name) a,
             (SELECT tablespace_name,
                          SUM(bytes) FREE_SPACE,
                          ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
               FROM
                          dba_free_space
              GROUP BY
                           tablespace_name) b
WHERE
a.tablespace_name = b.tablespace_name(+)
ORDER BY b.tablespace_name;

Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post