Tablespace Size

SELECT df.tablespace_name "TBLSPC_NAME",
  CASE 'GB'
    WHEN 'GB'
    THEN ROUND (df.totalspace / 1024 / 1024 / 1024)
    WHEN 'TB'
    THEN ROUND (df.totalspace / 1024 / 1024 / 1024 / 1024)
    ELSE ROUND (df.totalspace / 1024 / 1024)
  END "TOTAL_GB",
  CASE 'GB'
    WHEN 'GB'
    THEN ROUND ((df.totalspace - fs.freespace) / 1024 / 1024 / 1024)
    WHEN 'TB'
    THEN ROUND ((df.totalspace - fs.freespace) / 1024 / 1024 / 1024 / 1024)
    ELSE ROUND ((df.totalspace - fs.freespace) / 1024 / 1024)
  END "USED_GB",
  CASE 'GB'
    WHEN 'GB'
    THEN ROUND (fs.freespace / 1024 / 1024 / 1024)
    WHEN 'TB'
    THEN ROUND (fs.freespace / 1024 / 1024 / 1024 / 1024)
    ELSE ROUND (fs.freespace / 1024 / 1024)
  END "FREE_GB",
  ROUND (100 * (fs.freespace / df.totalspace)) "PCT_FREE",
  CASE 'GB'
    WHEN 'GB'
    THEN ROUND ((df.totalmaxspace - (df.totalspace - fs.freespace)) / 1024 / 1024 / 1024)
    WHEN 'TB'
    THEN ROUND ((df.totalmaxspace - (df.totalspace - fs.freespace)) / 1024 / 1024 / 1024 / 1024)
    ELSE ROUND ((df.totalmaxspace - (df.totalspace - fs.freespace)) / 1024 / 1024)
  END "MX_FREE_GB",
  ROUND (100 * ((df.totalmaxspace - (df.totalspace - fs.freespace)) / df.totalmaxspace)) "PCT_MX_FREE"
FROM
  (SELECT tablespace_name,
    SUM(bytes) TotalSpace,
    SUM(
    CASE
      WHEN maxbytes != 0
      THEN maxbytes
      ELSE bytes
    END) TotalMaxSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  UNION
  SELECT tablespace_name,
    SUM(bytes) TotalSpace,
    CASE
      WHEN SUM(maxbytes) = 0
      THEN SUM(bytes)
    END TotalMaxSpace
  FROM dba_temp_files
  GROUP BY tablespace_name
  ) df,
  (SELECT tablespace_name,
    SUM(bytes) FreeSpace,
    0
  FROM dba_free_space
  GROUP BY tablespace_name
  ) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY pct_mx_free;