쿼리로 보는 테이블스페이스 현황

** 순수 현재 할당량 중심

SELECT

    SUBSTR(a.tablespace_name, 1, 30) tablespace,

    ROUND(SUM(a.total1) / 1024 / 1024, 1) "TotalMB",

    ROUND(SUM(a.total1) / 1024 / 1024, 1) - ROUND(SUM(a.sum1) / 1024 / 1024, 1) "UsedMB",

    ROUND(SUM(a.sum1)   / 1024 / 1024, 1) "FreeMB",

    ROUND((ROUND(SUM(a.total1) / 1024 / 1024, 1) - ROUND(SUM(a.sum1) / 1024 / 1024, 1)) / ROUND(SUM(a.total1) / 1024 / 1024, 1) * 100, 2) "Used%"

FROM (

    SELECT

        tablespace_name, 0 total1, SUM(bytes) sum1, MAX(bytes) MAXB, COUNT(bytes) cnt

    FROM dba_free_space

    GROUP BY tablespace_name

    UNION

    SELECT

        tablespace_name, SUM(bytes) total1, 0, 0, 0

    FROM dba_data_files

    GROUP BY tablespace_name

) a

GROUP BY a.tablespace_name

ORDER BY tablespace;

---

** Auto extend와 Datafile의 Maximum space를 기준을 참고

SELECT 

    SUBSTR(a.tablespace_name, 1, 30) AS tablespace, 

    maxb  || ' GB' AS "Max", 

    usedb || ' GB' AS "Used", 

    freeb || ' MB' AS "Free"  

FROM (

    SELECT

        tablespace_name, 

        ROUND(SUM(maxbytes) / 1024 / 1024 , 1) - ROUND(SUM(bytes) / 1024 / 1024 , 1) as freeb, 

        ROUND(SUM(maxbytes) / 1024 / 1024 / 1024, 1) as maxb, 

        ROUND(SUM(bytes) / 1024 / 1024 / 1024, 1)    as usedb

    FROM dba_data_files

    GROUP BY tablespace_name

) a

ORDER BY a.usedb DESC

---

** 관련글 모음

[개발 - 예제 모음/ORACLE] - 테이블 스페이스 DATAFILE 추가 확장