개발 - 예제 모음/ORACLE

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

곰! 2008. 12. 23. 10:16
** 순수 현재 할당량 중심
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