col "表空间" for a10
col "总量(M)" for 99999999
col "已用(M)" for 99999999
col "剩余(M)" for 99999999
col "使用率(%)"  for 99.99
set linesize 200
set pages 99

SELECT D.TABLESPACE_NAME "表空间",
       MAXBYTES "总量(M)",
       SPACE - NVL(FREE_SPACE, 0) "已用(M)",
       MAXBYTES - (SPACE - NVL(FREE_SPACE, 0)) "剩余(M)",
       ROUND((SPACE - NVL(FREE_SPACE, 0)) / MAXBYTES * 100, 2) "使用率(%)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) /
                     (1024 * 1024),
                     2) MAXBYTES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME "表空间",
       MAXBYTES "总量(M)",
       NVL(USED_SPACE, 0) "已用(M)",
       MAXBYTES - NVL(USED_SPACE, 0) "剩余(M)",
       ROUND(NVL(USED_SPACE, 0) / MAXBYTES * 100, 2) "使用率(%)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) /
                     (1024 * 1024),
                     2) MAXBYTES
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE,
               ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM V$TEMPSEG_USAGE
         GROUP BY TABLESPACE) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)
 ORDER BY "使用率(%)" DESC;