Page History

WITH TBLSP_TOTALSPACETOTAL AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024))) TOTALSPACEALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) TOTALMAXSPACEMAX_MB
  FROM   DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
),
TBLSP_USEDSPACEFREE AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACEFREE_MB
  FROM   DBA_SEGMENTSFREE_SPACE
  GROUP BY TABLESPACE_NAME
)
SELECT TS.TABLESPACE_NAME           "TABLESPACE",
       US.TOTALUSEDSPACE (TS.ALLOCATED_MB-FS.FREE_MB)  "Used MB",
      (TS.TOTALSPACE - US.TOTALUSEDSPACE)  FS.FREE_MB       "Free MB",
       TS.TOTALSPACEALLOCATED_MB  "Total MB",
       TS.TOTALMAXSPACE MAX_MB        "Total Max MB",
       ROUND(100*((TS.TOTALMAXSPACE - US.TOTALUSEDSPACE)/ TS.TOTALMAXSPACEMAX_MB-(TS.ALLOCATED_MB-FS.FREE_MB))/TS.MAX_MB)) "Pct. Free"
FROM   TBLSP_TOTALSPACETOTAL TS,
       TBLSP_USEDSPACEFREE  USFS
WHERE  USFS.TABLESPACE_NAME = TS.TABLESPACE_NAME;