ORACLE查看表空间使用率

SELECT E.TABLESPACE_NAME,

       ROUND(SUM(E.TOTAL_SPACE), 2) "TOTAL_SPACE(G)",

       ROUND(SUM(E.FREE_SPACE), 2) "FREE_SPACE(G)",

       ROUND((1 - SUM(E.FREE_SPACE) / SUM(E.TOTAL_SPACE)) * 100, 2) "USED_RATE(%)"

  FROM (SELECT A.FILE_ID,

A.TABLESPACE_NAME,

DECODE(A.AUTOEXTENSIBLE, 'YES', A.MAXBYTES, A.BYTES) /

               (1024 * 1024 * 1024) TOTAL_SPACE,

DECODE(A.AUTOEXTENSIBLE,

                      'YES',

                      A.MAXBYTES - A.BYTES + D.F_SPACE,

                      D.F_SPACE) / (1024 * 1024 * 1024) FREE_SPACE

          FROM DBA_DATA_FILES A,

               (SELECT B.FILE_ID, SUM(B.BYTES) F_SPACE

                  FROM DBA_FREE_SPACE B

                 GROUP BY B.FILE_ID) D

         WHERE A.FILE_ID = D.FILE_ID(+)) E

 GROUP BY E.TABLESPACE_NAME

UNION ALL

SELECT E.TABLESPACE_NAME,

       ROUND(SUM(E.TOTAL_SPACE), 2) "TOTAL_SPACE(G)",

       ROUND(SUM(E.FREE_SPACE), 2) "FREE_SPACE(G)",

ROUND((1 - SUM(E.FREE_SPACE) / SUM(E.TOTAL_SPACE)) * 100, 2) "USED_RATE(%)"

  FROM (SELECT A.FILE_ID,

A.TABLESPACE_NAME,

ROUND(DECODE(A.AUTOEXTENSIBLE, 'YES', A.MAXBYTES, A.BYTES) /

                     (1024 * 1024 * 1024),

                     2) TOTAL_SPACE,

ROUND(DECODE(A.AUTOEXTENSIBLE,

                            'YES',

A.MAXBYTES - A.BYTES + D.F_SPACE,

                            D.F_SPACE) / (1024 * 1024 * 1024),

                     2) FREE_SPACE

          FROM DBA_TEMP_FILES A,

               (SELECT B.FILE_ID, SUM(B.BYTES_FREE) F_SPACE

                  FROM V$TEMP_SPACE_HEADER B

                 GROUP BY B.FILE_ID) D

         WHERE A.FILE_ID = D.FILE_ID(+)) E

 GROUP BY E.TABLESPACE_NAME

ORDER BY 4 DESC;