테이블 스페이스 용량 보기

select B.TS_NAME as "TableSpace",
       TO_CHAR(SUM(NVL(A.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "전체용량(M)",
       TO_CHAR(SUM(NVL(A.TOT_BYTES/1024/1024,0))-SUM(NVL(B.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "사용한공간(M)",      
       ROUND(((SUM(NVL(A.TOT_BYTES,0))-SUM(NVL(B.TOT_BYTES,0)))/SUM(A.TOT_BYTES))*100,1) AS "사용한공간(%)",
       TO_CHAR(SUM(NVL(B.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "남은용량(M)",
        ROUND((SUM(NVL(B.TOT_BYTES,0))/SUM(A.TOT_BYTES)*100),1) AS "남은용량(%)"
FROM (SELECT TABLESPACE_NAME AS "TS_NAME" , SUM(BYTES) AS "TOT_BYTES"
      FROM DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) B, (SELECT TABLESPACE_NAME AS "TS_NAME",SUM(BYTES)  AS "TOT_BYTES"
                                    FROM DBA_DATA_FILES
                                    GROUP BY TABLESPACE_NAME) A
WHERE B.TS_NAME=A.TS_NAME 
GROUP BY B.TS_NAME
ORDER BY B.TS_NAME;

 

 

 

3월말쯤에 이거 짜는데 2시간 넘게 걸렸던 기억이 납니다.....

키포인트는  FROM 절에 있습니다.

얼마나 해맸떤지;;
결과는 아래처럼나옵니다.

 

TableSpace                                전체용량(M)      사용한공간(M)    사용한공간(%) 남은용량(M)      남은용량(%)
------------------------------ ---------------- ---------------- ------------- ---------------- -----------
LOGMNRTS                                    510                0                      0                        510               100
SYSAUX                                         1,530            1,223                  79.9                    307                20.1
SYSTEM                                         2,040            1,172                  57.4                   868                42.6

..........................................................................

 

 

 

테이블 변경전에 테이블 스페이스 확인하면서 작업을 하려고 만든건데... 테이블 스페이스가 많다보니...조금 오래걸리기래
관련 업종 만 뽑아서 뿌려줄수있또록 응용해봤습니다
select B.TS_NAME as "TableSpace",
       --TO_CHAR(SUM(A.BYTES),'999,999,999,999') AS "전체용량(BYTES)",
       TO_CHAR(SUM(NVL(A.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "전체용량(M)",
       TO_CHAR(SUM(NVL(A.TOT_BYTES/1024/1024,0))-SUM(NVL(B.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "사용한공간(M)",      
       ROUND(((SUM(NVL(A.TOT_BYTES,0))-SUM(NVL(B.TOT_BYTES,0)))/SUM(A.TOT_BYTES))*100,1) AS "사용한공간(%)",
       TO_CHAR(SUM(NVL(B.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "남은용량(M)",
        ROUND((SUM(NVL(B.TOT_BYTES,0))/SUM(A.TOT_BYTES)*100),1) AS "남은용량(%)"
FROM (SELECT TABLESPACE_NAME AS "TS_NAME" , SUM(BYTES) AS "TOT_BYTES"
      FROM DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) B, (SELECT TABLESPACE_NAME AS "TS_NAME",SUM(BYTES)  AS "TOT_BYTES"
                                    FROM DBA_DATA_FILES
                                    GROUP BY TABLESPACE_NAME) A
WHERE B.TS_NAME=A.TS_NAME
AND B.TS_NAME LIKE 'TS_&업무_약어_입력%'
GROUP BY B.TS_NAME
ORDER BY B.TS_NAME;

실행하면 아래처럼 입력하라고 나옵니다.

 

Enter value for 업무_약어_입력: SDA

old  14: AND B.TS_NAME LIKE 'TS_&업무_약어_입력%'
new  14: AND B.TS_NAME LIKE 'TS_SDA%'

 

 

TableSpace                                  전체용량(M)      사용한공간(M)    사용한공간(%) 남은용량(M)      남은용량(%)
------------------------------ ---------------- ---------------- ------------- ---------------- -----------
 SDA_A0001                                   1,020                  525                  51.5                 495                48.5
 SDA_X0001                                     510                    0                    0                     510               100

 

 

걍 올려봅니다...