Checking tablespace Usage & Availability in Oracle

This is a little bit of code that I keep to hand for viewing how much of the allocated disk space is used in an oracle tablespace. I’m not sure where I stole the original from, but it has been modified since.

View each tablespace in oracle and the amount of allocated disk space used. Note that the amount and percent “free” doesn’t take into account cases where the tablespace can grow automatically.

--------------------------------------------------------------------------------
-- A report for checking disk space/tablespace usage
--------------------------------------------------------------------------------
WITH df AS (
SELECT tablespace_name,
       ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
 GROUP BY tablespace_name
), tu AS (
SELECT round(SUM(bytes)/(1024*1024)) totalusedspace,
       tablespace_name
  FROM dba_segments
 GROUP BY tablespace_name
)
SELECT df.tablespace_name                                                       "Tablespace",
       totalusedspace                                                           "Used MB",
       (df.totalspace - tu.totalusedspace)                                      "Free MB",
       df.totalspace                                                            "Total MB",
       CAST( totalusedspace/1024 AS NUMBER(20,2))                               "Used GB",
       CAST((df.totalspace/1024 - tu.totalusedspace/1024) AS NUMBER(20,2))      "Free GB",
       CAST( df.totalspace/1024 AS NUMBER(20,2))                                "Total GB",
       round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))       "Pct. Free",
       100 - round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Used"
  FROM df
    INNER JOIN tu
       ON df.tablespace_name = tu.tablespace_name
 ORDER BY df.tablespace_name
;