[오라클]테이블 스페이스 용량 자동 증가 및 추가하기

출처 : http://iloveppj.tistory.com/107

OS : Windows Server2008
프로그램 및 버전 : Oracle11g

※ 주의사항 : 실제 적용을 위해서는 한번쯤 시험을 해보고 하시길...

1. 작업개요
   - 처음 테이블 스페이스를 생성할때 사이즈를 지정하고 데이터가 많아지다 보면 지정된 사이즈를 넘어서게 되고 그렇게 되면 DB가 멈춰 버린다. 이것을 방지하기위해 테이블스페이스 사이즈를 자동으로 증가할 수 있게 설정해줘야 하고, 만일 허용치(30G)가 넘어가려 한다면 데이터 파일을 새로 생성해줘야한다.

2. 작업내용
1) 테이블 스페이스 확인하기 : 현재 테이블스페이스의 총 사이즈와, 사용중인 사이즈, 남은 사이즈를 확인할 수 있음

SELECT A.TABLESPACE_NAME          TABLESPACE_NAME
            ,ROUND(A.BYTES,2)           TOT_SIZE
            ,ROUND((A.BYTES-B.BYTES),2) USE_SIZE
            ,ROUND(B.BYTES,2) FREE_SIZE
   FROM (SELECT B.TABLESPACE_NAME
                         ,SUM(B.BYTES)/1024/1024/1024 BYTES
                 FROM DBA_DATA_FILES B
               GROUP BY B.TABLESPACE_NAME
             ) A
            ,(SELECT A.TABLESPACE_NAME
                         ,SUM(A.BYTES)/1024/1024/1024 BYTES
                  FROM DBA_FREE_SPACE A
                 GROUP BY A.TABLESPACE_NAME
            ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 1

- 하나의 데이터파일 당 최고 값이 30G이므로 만일 TOTAL사이즈가 30G가 되어간다면 참고1) 을 참고하여 데이터파일을 새로 생성해 줘야 한다.

2) 테이블 스페이스가 자동으로 증가되도록 설정되어 있는지 확인하기

SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES

- AUTOEXTENSIBLE : YES이면 자동 증가, NO 이면 자동증가 아님

3) 테이블스페이스 사이즈 자동 증가하도록 설정하기

ALTER DATABASE DATAFILE ['데이터파일경로 및 파일명'] AUTOEXTEND ON NEXT [사이즈];
- 선택한 데이터파일을 자동으로 설정한 사이즈 만큼 증가한다.
ALTER DATABASE DATAFILE ['데이터파일경로 및 파일명'] AUTOEXTEND ON MAXSIZE [사이즈];
- 선택한 데이터파일이 설정한 사이즈까지 증가한다.

ex) ALTER DATABASE DATAFILE 'D:\MYDB\MY_DATA.ORA' AUTOEXTEND ON NEXT 100M;
ex) ALTER DATABASE DATAFILE 'D:\MYDB\MY_DATA.ORA' AUTOEXTEND ON MAXSIZE 30000M;

참고1) 

ALTER TABLESPACE [테이블스페이스명] ADD DATAFILE ['데이터파일경로 및 파일명'] SIZE [사이즈];
ex) ALTER TABLESPACE MYDB ADD DATAFILE 'D:\MYDB\MY_DATA.ORA' SIZE 10G;

3. 작업결과

- 만일 테이블스페이스 사이즈가 30G에 도달하여 참고1 작업을 진행하였다면, 1) 을 다시 실행하여 정상적으로 증가됐는지 확인한다.

0. 데이터 삭제 후 사이즈 반영 위해서는.. 아래처럼 rebuild 해야 된다고 한다..

alter table DYN_WEEKSALES_MB move tablespace GSCMMB_DATA;

alter index DYN_WEEKSALES_MB_INDEX1 rebuild tablespace GSCMMB_DATA;

alter index DYN_WEEKSALES_MB_INDEX2 rebuild tablespace GSCMMB_DATA;

alter index DYN_WEEKSALES_MB_INDEX3 rebuild tablespace GSCMMB_DATA;

0. 오라클 DB의 테이블 크기를 확인하기 위해서는

SELECT A.SEGMENT_NAME,

  A.SEGMENT_TYPE,

  ROUND(SUM(A.BYTES)/1024/1024) AS "SIZE(MB)"

FROM DBA_SEGMENTS A,

  DBA_TABLES B

WHERE 1            =1

AND A.SEGMENT_NAME = B.TABLE_NAME

AND A.OWNER        = 'GSCMMB'

GROUP BY A.SEGMENT_NAME,

  A.SEGMENT_TYPE

ORDER BY A.SEGMENT_NAME ;