[Oracle] 테이블스페이스 관리

---

웹 운영을 하다보면 이따금씩 문제가 발생하는 테이블스페이스 정리 한번 하고 넘어가겠습니다.

간단하게 말하면 데이터베이스 운용간 공간을 사용하는 건데, 항상 가득가득 차서 데이터들 저장안되고 고객들도 안돼요~ 하고 문제 찾아보면 해당 용량 갠찮나? 라고 찾아봐야 하는 우선순위 높은 점검 대상입니다.

 

테이블 스페이스란?

테이블 스페이스(Tablespace)는 데이터베이스에서 데이터가 물리적으로 저장되는 영역을 관리하는 논리적 개념입니다. 데이터베이스는 데이터를 효율적으로 저장하고 검색하기 위해 테이블, 인덱스, 기타 객체들을 물리적으로 파일 시스템에 저장하는데, 테이블 스페이스는 이러한 저장소를 논리적으로 그룹화한 것입니다.

 

테이블 스페이스 주요특징

 

• 논리적 스토리지 단위: 테이블 스페이스는 데이터 파일의 논리적 모음으로, 테이블과 인덱스 같은 데이터베이스 객체가 저장됩니다.
• 데이터 파일과의 관계: 테이블 스페이스는 하나 이상의 데이터 파일로 구성됩니다. 각 데이터 파일은 물리적으로 데이터베이스 서버의 디스크에 저장됩니다.
• 관리 효율성: 대용량 데이터베이스에서 테이블 스페이스를 사용하면 데이터를 논리적으로 분할하여 관리할 수 있어 유지보수와 성능 최적화에 도움이 됩니다.
• 데이터 격리: 서로 다른 데이터베이스 사용자나 애플리케이션에서 생성한 데이터를 다른 테이블 스페이스에 저장할 수 있어 데이터의 격리 및 보안을 향상할 수 있습니다.

 

테이블 스페이스 사용 예

• 시스템 테이블 스페이스: 데이터베이스의 시스템 데이터를 저장하는 테이블 스페이스입니다.
• 사용자 테이블 스페이스: 사용자가 생성한 테이블과 인덱스를 저장하는 데 사용됩니다.
• 임시 테이블 스페이스: 정렬 작업 등과 같은 일시적인 작업에 필요한 데이터를 저장하는 데 사용됩니다.

예를 들어, Oracle과 같은 데이터베이스 관리 시스템에서는 SYSTEM, SYSAUX, USERS와 같은 기본적인 테이블 스페이스를 제공하여 각기 다른 데이터베이스 작업과 데이터를 관리합니다.

테이블 스페이스를 통해 데이터 파일을 관리하고 분리하면 데이터베이스 성능을 높이고 관리의 편의성을 높일 수 있습니다.

 

 

테이블 스페이스 용량 확인 쿼리

SELECT A.TABLESPACE_NAME "TABLESPACE Name",
       ROUND(MAXBYTES/1048576,2) "최대용량(MB)",
       ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) "할당용량(MB)",
       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) "여유용량(MB)",
       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) "사용량(MB)",
       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) "여유%",
       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) "사용%"
FROM   ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES) BYTES_ALLOC,
                SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
         FROM DBA_DATA_FILES F
         GROUP BY TABLESPACE_NAME) A,
       ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES)  BYTES_FREE
         FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
      UNION
      SELECT TABLESPACE_NAME,
             ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
             ROUND(SUM(BYTES_FREE) / 1048576,2),
             ROUND(SUM(BYTES_USED) / 1048576,2),
             ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
             100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
             ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
      FROM   SYS.V_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
ORDER BY 1;

쿼리 실행결과 예시

 

테이블 스페이스 추가 방법

1. 테이블 스페이스 파일명확인

SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, STATUS
   FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME = 'TS_ACCD_DAT';  -- 위 TABLESPACE Name을 넣는다.

 

쿼리 실행 결과 예시

2. 파일 추가

ALTER TABLESPACE TS_GW_DAT ADD DATAFILE '/oracle/oradata/ACCDB/ts_accd_dat_12.dbf' SIZE 30000m;
-- 30000m 은 약 30GB

 

테이블 스페이스 자동 증가 설정

SELECT *
   FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME = 'TS_ACCD_DAT';  -- 위 TABLESPACE Name을 넣는다.

 

AUTOEXTENSIBLE 컬럼과 INCREMENT_BY 컬럼 확인

AUTOEXTENSIBLE 이 YES가 아니면 자동 증가가 되지 않습니다.

-- 자동 용량 늘어나는 설정
ALTER DATABASE DATAFILE '/oracle/oradata/ACCDB/ts_accd_dat_12.dbf' AUTOEXTEND ON NEXT 1000m;

 

요약

위와 같이 따라했는데도 문제가 발생하는 경우엔 물리적 용량도 체크 해봐야합니다. :-(

그리고 아무래도 해당 용량이 계속 가득 차는 경우에는 소스 코드 또는 로그테이블 쪽 심히 많이 쌓이는 경우가 많습니다.

제 경우에는 전자정부프레임워크쪽 자동으로 설정된 시스템 로그파일 때문에 문제가 많았네요 ㅎㅎ

 

그럼 많은 도움이 되셨길 바라며 이만 끝