오라클 테이블 스페이스 용량 구하기

드래그 가능합니다.

/*
 * 오라클 테이블 스페이스 사용 용량 쿼리
 */

SELECT
    DECODE(GROUPING(TABLESPACE_NAME), 1, '합계', TABLESPACE_NAME) AS TABLESPACE_NAME
    , SUM(TOTAL_MB) AS "총량(MB)"
    , SUM(USED_MB) AS "사용량(MB)"
    , SUM(FREE_MB) AS "남은량(MB)"
    , ROUND(SUM(USED_MB) / SUM(TOTAL_MB) * 100, 2)  AS "사용률"
FROM (
        SELECT
            SUBSTR(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME
            , ROUND(SUM(TOTAL) / 1024 / 1024) AS TOTAL_MB
            , ROUND(SUM(TOTAL) / 1024 / 1024) - ROUND(SUM(SUM_BYTES) / 1024 / 1024) AS USED_MB
            , ROUND(SUM(SUM_BYTES) / 1024 / 1024) AS FREE_MB
        FROM (
                SELECT
                    TABLESPACE_NAME
                    , 0 AS TOTAL
                    , SUM(BYTES) AS SUM_BYTES
                FROM DBA_FREE_SPACE
                GROUP BY TABLESPACE_NAME
                UNION
                SELECT
                    TABLESPACE_NAME
                    , SUM(BYTES) AS TOTAL
                    , 0 AS SUM_BYTES
                FROM DBA_DATA_FILES
                GROUP BY TABLESPACE_NAME
            )
        GROUP BY TABLESPACE_NAME
)
GROUP BY ROLLUP (TABLESPACE_NAME)
ORDER BY TABLESPACE_NAME