テーブルスペース利用率(Oracle)

テーブルスペース利用率.sql

-- テーブルスペース利用率
!BIND SET TBSNAME='%' -- テーブルスペース名称
SELECT
     TABLESPACE_NAME ,
     TO_CHAR( NVL(TOTAL_BYTES / 1024 / 1024, 0) , '999,999,999' ) AS "SIZE(MB)" ,
     TO_CHAR( NVL( (TOTAL_BYTES - FREE_TOTAL_BYTES) / 1024 / 1024 , 0 ) , '999,999,999' ) AS "USED(MB)" ,
     TO_CHAR( NVL(FREE_TOTAL_BYTES / 1024 / 1024, 0) , '999,999,999' ) AS "FREE(MB)" ,
     ROUND( NVL( (TOTAL_BYTES - FREE_TOTAL_BYTES) / TOTAL_BYTES * 100 , 100 ) , 2 ) AS "RATE(%)"
FROM
     (
          SELECT
               TABLESPACE_NAME ,
               SUM(BYTES) TOTAL_BYTES
          FROM
               DBA_DATA_FILES
          GROUP BY
               TABLESPACE_NAME
     ) ,
     (
          SELECT
               TABLESPACE_NAME FREE_TABLESPACE_NAME ,
               SUM(BYTES) FREE_TOTAL_BYTES
          FROM
               DBA_FREE_SPACE
          GROUP BY
               TABLESPACE_NAME
     )
WHERE     1 = 1
     AND TABLESPACE_NAME = FREE_TABLESPACE_NAME(+)
     AND TABLESPACE_NAME LIKE '%' || :TBSNAME || '%';