테이블스페이스 및 파일 용량 조회하기 쿼리

테이블스페이스 조회 쿼리

SELECT A.TABLESPACE_NAME AS "테이블스페이스명",
    ROUND(A.BYTES / 1024 / 1024, 2) AS "총_크기(MB)",
    ROUND((A.BYTES-B.FREE) / 1024 / 1024, 2) AS "사용량(MB)",
    ROUND(B.FREE / 1024/1024, 2) AS "여유공간(MB)",
    ROUND(((A.BYTES-B.FREE) / A.BYTES) * 100, 2) AS "사용률(%)"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS BYTES
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) A,
     (SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE
         FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY "사용률(%)" DESC;

파일 용량 조회

SELECT A.TABLESPACE_NAME AS "테이블스페이스",
    A.FILE_NAME AS "파일경로",
    A.AUTOEXTENSIBLE AS "자동증가",
    ROUND ((A.BYTES / 1024 / 1024 , 2) AS "현재크기",
    ROUND (A.BYTES - NVL(B.FREE,0)) / 1024 / 1024, 2) AS "사용량(MB),
    ROUND(NVL(B.FREE, 0) / 1024 / 1024, 2) AS "여유공간(MB)",
    ROUND(((A.BYTES - NVL(B.FREE, 0)) / A.BYTES) * 100, 2) AS "사용률(MB)
FROM DBA_DATA_FILES A,
    (SELECT FILE_ID, SUM(BYTES) FREE
        FROM DBA_FREE_SPACE
        GROUP BYF FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID(+)
ORDER BY "사용률(%)" DESC, A.TABLESPACE_NAME, A.FILE_NAME;