TABLESPACE_SIZE - mieyanman @ ウィキ

WITH
T1 AS
(
SELECT FILE_NAME

     ,FILE_ID
     ,TABLESPACE_NAME
     ,BYTES
     ,BLOCKS
     ,STATUS
     ,RELATIVE_FNO
     ,AUTOEXTENSIBLE
     ,MAXBYTES
     ,MAXBLOCKS
     ,INCREMENT_BY
     ,USER_BYTES
     ,USER_BLOCKS
     ,ONLINE_STATUS
 FROM DBA_DATA_FILES

)
,
T2 AS
(
SELECT TABLESPACE_NAME

     ,FILE_ID
     ,BLOCK_ID
     ,BYTES
     ,BLOCKS
     ,RELATIVE_FNO
 FROM DBA_FREE_SPACE

)
,
T3 AS
(

   SELECT T1.TABLESPACE_NAME AS TABLESPACE_NAME
         ,ROUND(MIN(T1.BYTES) / (1024 * 1024), 2) AS NOW_SIZE
         ,ROUND(MIN(T1.BYTES) / (1024 * 1024) - SUM(T2.BYTES) / (1024 * 1024),2) AS USED_SIZE
     FROM T1
       LEFT OUTER JOIN T2
         ON T1.FILE_ID = T2.FILE_ID
     GROUP BY T1.TABLESPACE_NAME

)
SELECT T3.TABLESPACE_NAME AS "表領域"

     ,TO_CHAR(T3.NOW_SIZE,'FM9,999,999,990')  AS "現在のサイズ(MB)"
     ,TO_CHAR(T3.USED_SIZE,'FM9,999,999,990') AS "使用量(MB)"
     ,TO_CHAR(ROUND(T3.USED_SIZE / T3.NOW_SIZE * 100,2),'FM9,999,999,990.00') AS "使用率(%)"
 FROM T3