[오라클]Tablespace Resize 처리순서

1. 테이블 스페이스별 현재파일용량 / 사용량 / 미사용량 확인
2. 테이블 스페이스 최적화 가능 용량 확인
3. 테이블 스페이스 최소용량으로 RESIZE 하기

------------------------------------------------------------------

 -- 테이블스페이스별 현재파일용량/사용량/미사용량 확인
SELECT
    DECODE(GROUPING(TABLESPACE_NAME), 1, 'TOTAL', TABLESPACE_NAME) AS TABLESPACE_NAME
    , SUM(TOTAL_MB) AS TOTAL_GB
    , SUM(USED_MD)  AS USED_GB
    , SUM(FREE_MB)  AS FREE_GB
    , TO_CHAR(ROUND(SUM(USED_MD) / SUM(TOTAL_MB) * 100, 2), '90.99') || ' %'  AS USAGED
  FROM (
        SELECT
            SUBSTR(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME
            , ROUND(SUM(TOTAL) / POWER(1024, 3), 2) AS TOTAL_MB
            , ROUND(SUM(TOTAL) / POWER(1024, 3), 2) - ROUND(SUM(SUM_BYTES) /POWER(1024, 3), 2) AS USED_MD
            , ROUND(SUM(SUM_BYTES) / POWER(1024, 3), 2) AS FREE_MB
          FROM (
                SELECT
                    TABLESPACE_NAME
                    , 0 AS TOTAL
                    , SUM(BYTES) AS SUM_BYTES
                  FROM DBA_FREE_SPACE
                  GROUP BY TABLESPACE_NAME
                  UNION
                  SELECT
                    TABLESPACE_NAME
                    , SUM(BYTES) AS TOTAL
                    , 0 AS SUM_BYTES
                    FROM DBA_DATA_FILES
                    GROUP BY TABLESPACE_NAME
               )
      GROUP BY TABLESPACE_NAME
    )
    GROUP BY ROLLUP (TABLESPACE_NAME)
    ORDER BY TABLESPACE_NAME;
   
   
------------------------------------------------------------------

-- 테이블스페이스 최적화 가능 용량 확인
SELECT FILE_NAME,
       CEIL ( (NVL(HWM, 1) * 8192) / 1024 / 1024) SMALLEST,
       CEIL ( BLOCKS * 8192 / 1024 / 1024) CURRSIZE,
       CEIL (BLOCKS * 8192 / 1024 /1024) -
       CEIL ( ( NVL(HWM, 1) * 8192) / 1024 / 1024 ) SAVINGS
  FROM DBA_DATA_FILES A,
       ( SELECT FILE_ID, MAX(BLOCK_ID + BLOCKS - 1) HWM
           FROM DBA_EXTENTS
          GROUP BY FILE_ID ) B
 WHERE A.FILE_ID = B.FILE_ID(+)
 
 
 -- 테이블스페이스 최소용량으로 RESIZE 하기 [주의]
 --ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/GWARE01.dbf' RESIZE 1024M;