[Oracle] Table Space 사용량 확인 쿼리

 

SELECT A.TABLESPACE_NAME
     , ROUND(SUM(A.BYTES) / (1024 * 1024)) "전체(MB)"
     , ROUND(SUM(B.FREES) / (1024 * 1024)) "여유(MB)"
     , ROUND(SUM(B.FREES) * 100 / SUM(A.BYTES), 2) AS "잔여공간비율(%)"
  FROM (
        SELECT FILE_ID
             , TABLESPACE_NAME
             , SUM(BYTES) BYTES
          FROM DBA_DATA_FILES
         GROUP BY FILE_ID, TABLESPACE_NAME) A
     , (
        SELECT TABLESPACE_NAME
             , FILE_ID
             , SUM(BYTES) FREES
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME, FILE_ID) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.FILE_ID = B.FILE_ID
 GROUP BY A.TABLESPACE_NAME ;

 

	SELECT A.TABLESPACE_NAME,
               ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,
               ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,
               ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,
               ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,
               100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,
              ROUND(MAXBYTES/1048576,2) MAX_SIZE
        FROM   ( SELECT F.TABLESPACE_NAME,
                        SUM(F.BYTES) BYTES_ALLOC,
                        SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
                 FROM DBA_DATA_FILES F
                 GROUP BY TABLESPACE_NAME) A,
               ( SELECT F.TABLESPACE_NAME,
                        SUM(F.BYTES)  BYTES_FREE
                 FROM DBA_FREE_SPACE F
                 GROUP BY TABLESPACE_NAME) B
        WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
        UNION
        SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
               ROUND(SUM(BYTES_FREE) / 1048576,2),
               ROUND(SUM(BYTES_USED) / 1048576,2),
               ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
               100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
               ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
        FROM   SYS.V_$TEMP_SPACE_HEADER
        GROUP BY TABLESPACE_NAME
        ORDER BY 1;