SELECT D.TABLESPACE_NAME,

       SPACE "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",

       FREE_SPACE "FREE_SPACE(M)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

               SUM(BLOCKS) BLOCKS

          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 --if have tempfile

SELECT D.TABLESPACE_NAME,

       SPACE "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       USED_SPACE "USED_SPACE(M)",

       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

       NVL(FREE_SPACE, 0) "FREE_SPACE(M)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

               SUM(BLOCKS) BLOCKS

          FROM DBA_TEMP_FILES

         GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

          FROM V$TEMP_SPACE_HEADER

         GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

 

select tu.tablespace_name as "tablespace_name",

       round((tu.bytes + tf.bytes) / 1024 / 1024 / 1024, 2) as "sum G",

       round(tu.bytes / 1024 / 1024 / 1024, 2) as "ts_used G",

       round(tf.bytes / 1024 / 1024 / 1024, 2) as "ts_free G",

       round(tu.bytes / (tu.bytes + tf.bytes) * 100, 2) || '%' as "per_used"

  from sys.sm$ts_used tu, sys.sm$ts_free tf

where tu.tablespace_name = tf.tablespace_name;