Page History

WITH TBLSP_TOTAL AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) ALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) MAX_MB
  FROM   DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) ALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) MAX_MB
  FROM   DBA_TEMP_FILES
  GROUP BY TABLESPACE_NAME),
TBLSP_FREE AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) FREE_MB
  FROM   DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME,
         ROUND(FREE_SPACE/(1024*1024)) FREE_MB
  FROM   DBA_TEMP_FREE_SPACE),
TBLSP_USED AS (
  SELECT TS.TABLESPACE_NAME           "TABLESPACE"         TABLESPACE_NAME,
         NVL((TS.ALLOCATED_MB-FS.FREE_MB), 0) "Used USED_MB",
         NVL(FS.FREE_MB, 0)      "Free MB"              FREE_MB,
         TS.ALLOCATED_MB  "Total MB"                     TOTAL_MB,
         TS.MAX_MB        "Total Max MB",        ROUND(100*((TS.MAX_MB-(TS.ALLOCATED_MB-FS.FREE_MB))/TS.MAX_MB)) "Pct. Free"           TOTAL_MAX_MB
  FROM   TBLSP_TOTAL TS,
         TBLSP_FREE  FS
  WHERE  FS.TABLESPACE_NAME(+) = TS.TABLESPACE_NAME)
SELECT TABLESPACE_NAME  "TABLESPACE",
       USED_MB          "Used MB",
       FREE_MB          "Free MB",
       TOTAL_MB         "Total MB",
       TOTAL_MAX_MB     "Total Max MB",
       ROUND(100*((TOTAL_MAX_MB-USED_MB)/TOTAL_MAX_MB))  "Pct. Free"
FROM   TBLSP_USED
ORDER BY TS.TABLESPACE_NAME;