Oracle 테이블스페이스 자동 증설 쿼리

 

Oracle 테이블스페이스 증설

Oracle 데이터베이스에서 테이블스페이스는 데이터 파일을 논리적으로 그룹화한 단위로, 데이터 저장소로 활용됩니다. 데이터 양이 증가하거나 새로운 데이터베이스 객체를 생성할 때, 기존 테이블스페이스의 공간이 부족하면 증설이 필요합니다. 테이블스페이스를 증설하는 방법은 크게 두 가지입니다: 데이터 파일 추가와 기존 데이터 파일 크기 증가입니다.

1. 테이블스페이스에 데이터 파일 추가

새로운 데이터 파일을 추가하여 테이블스페이스를 증설하는 방법입니다. 테이블스페이스에 여러 데이터 파일을 추가할 수 있으며, 이는 데이터 분산 및 성능 향상에도 도움이 될 수 있습니다.

 

ALTER TABLESPACE [테이블스페이스_이름] 
ADD DATAFILE '[데이터파일_경로]' SIZE [크기] [옵션];

 

 

• 테이블스페이스_이름: 증설할 테이블스페이스의 이름
• 데이터파일_경로: 추가할 데이터 파일의 경로와 파일명
• 크기: 새로 추가할 데이터 파일의 크기 (MB, GB 등)
• 옵션:
  • AUTOEXTEND ON: 데이터 파일이 자동으로 확장되도록 설정
  • NEXT [크기]: 자동 확장 시 늘어나는 단위 설정
  • MAXSIZE [크기]: 자동 확장의 최대 크기 설정

 

 

ALTER DATABASE DATAFILE '[데이터파일_경로]' AUTOEXTEND ON NEXT [크기] MAXSIZE [크기];

※ 이런식으로 자동증설되게 할 순 있지만, 보통 용량 관리 이슈로 그렇게 하지 않음.

따라서 아래 쿼리를 스케줄러로 등록해서 증설할 수 있음

 

 

주의사항

• 디스크 공간 확인: 데이터 파일을 추가하거나 확장하기 전에 물리적 디스크 공간이 충분한지 확인해야 합니다.
• 백업: 테이블스페이스 증설 작업 전에 데이터베이스 백업을 수행하여 문제 발생 시 복구 가능성을 확보하는 것이 중요합니다.
• 자동 확장 제한 설정: 자동 확장을 사용할 때는 MAXSIZE를 설정하여 무한 확장을 방지하는 것이 좋습니다. 디스크 공간이 부족해지면 시스템 성능에 악영향을 미칠 수 있습니다.

 

---

 

 

아래 쿼리로 crontab 등록하면 자동 증설 가능

※ 운영 서버 특성에 따라 상세 조건 변경 필요

 

SELECT CASE WHEN ASMGROUP IS NULL THEN ''
       ELSE 'ALTER TABLESPACE '||TABLESPACE_NAME || ' ADD DATAFILE '||'''+'||ASMGROUP||''' SIZE '||EXTEND||'G AUTOEXTEND OFF;'
       END AS "QUERY"
          FROM (SELECT TABLESPACE_NAME,
                       CASE WHEN TOTAL < 500 THEN 10 ELSE 30 END AS "EXTEND", -- 원하는 용량으로 변경(GB단위)
                       (SELECT NAME
                          FROM (SELECT NAME
                                  FROM V$ASM_DISKGROUP
                                 WHERE NAME LIKE '%DATA%'
                                   AND FREE_MB > 100*1024
                                 ORDER BY FREE_MB DESC)
                         WHERE ROWNUM < 2) AS "ASMGROUP"
                  FROM (SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME ,
                               ROUND(SUM(A.TOTAL1) /1024/1024/1024, 1) "TOTAL",
                               ROUND(SUM(A.SUM1) /1024/1024/1024, 1) "FREE" ,
                               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,
                               DBA_TABLESPACES B
                         WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
                           AND B.STATUS='ONLINE'
                           AND B.CONTENTS NOT IN ('TEMPORARY','UNDO')
                           AND B.TABLESPACE_NAME NOT IN ('SYSTEM', 'SYSAUX', 'USERS')
                         GROUP BY A.TABLESPACE_NAME
                         ORDER BY TABLESPACE_NAME )
                 WHERE FREE < 30  -- 증설 기준 변경 (현재 : 30G이하이며 사용률이 90%이상일때 증설)
                   AND USED > 90 );