나의 향기에 젖다...

## 테이블 사이즈 조회 쿼리

SELECT A.SEGMENT_NAME,
             ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",

             ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB",
             A.SEGMENT_TYPE
  FROM DBA_SEGMENTS A,
            DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
     AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
 ORDER BY 2 DESC;

## 인덱스 사이즈 조회 쿼리

SELECT A.SEGMENT_NAME,
             ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",

             ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB",
             A.SEGMENT_TYPE
  FROM DBA_SEGMENTS A,
            DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
     AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
 ORDER BY 2 DESC;

*** TABLE PARTITION

 세그먼트가 분할된 테이블을 말한다.

 분할에 사용되는 정보입력은 Value 이며 이는 Hash / Range / List 등으로 나뉘어진다.

// TABLESPACE_NAME 별로 합처리

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;

// 전체 합처리

SELECT  ROUND(SUM(A.TOTAL1)/1024/1024/1024,1) "TOTALGB",
              ROUND(SUM(A.TOTAL1)/1024/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024/1024,1) "USEDGB",
              ROUND(SUM(A.SUM1)/1024/1024/1024,1) "FREEGB",
              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