지금까지는 테이블스페이스를 전혀 신경안쓰고 테이블을 관리하고 있었는데
테이블스페이스 분리의 중요성을 느껴서
오라클 기준으로 테이블 스페이스를 공부하려고 합니다.

테이블스페이스란?

• 하나 또는 여러개의 데이터 파일로 구성되어 있는 논리적인 데이터 저장구조 입니다. 
• 논리적 단위 구조는 데이터블록 -> 익스텐트 -> 세그먼트 -> 테이블스페이스 구성이 되었습니다.
• 테이블스페이스는 데이터를 논리적으로 분리해서 저장하는 단위이기 때문에 I/O 성능과는 직접적인 관계가 없습니다.
  • 실제로 테이블스페이스에 대해서 ALTER 쿼리를 실행시키면 DB 대기 시간이 없더라고요

테이블스페이스 종류

• SYSTEM Tablespace
  Data Dictionary Table이 저장되는 공간으로 데이터베이스에서 가장 중요한 테이블스페이스 입니다. 중요한 데이터가 담겨져 있는 만큼 해당 테이블스페이스가 손상될 경우 오라클 서버는 정상적으로 구동되지 않습니다.
• SYSAUX Tablespace
  SYSAUX 테이블스페이스는 SYSTEM 테이블스페이스의 보조로 기존에 SYSTEM Tablespace에 있는 다양한 유틸리티 및 기능들 분리하여 저장한 공간입니다. SYSTEM과 마찬가지로 데이터베이스 운영에 필수적인 테이블스페이스이고 SYSTEM 테이블스페이스와 다르게 문제가 생겨도 시스템에 이상은 없지만 해당 테이블스페이스에 존재했던 기능들은 사용못하게 됩니다.
• UNDO Tablespace
  롤백을 위한 테이블스페이스입니다. 롤백하게 되는 경우를 대비하여 DML 작업이 발생했을 때 수정 이전의 값에 대한 정보를 UNDO Segment에 저장합니다. 이러한 UNDO Segment에 대한 관리 공간으로 UNDO 테이블스페이스를 사용하게되고 데이터베이스 운영에 있어서 필수적으로 최소 하나의 UNDO 테이블스페이스가 필요합니다.
• TEMPORARY Tablespace
  데이터를 임시적으로 저장하는 공간으로 오라클 서버가 재기동 되면 저장된 데이터는 초기화 되는 것이 특징입니다. (휘발성) 일반적으로 사용자 쿼리의 요청으로 정렬하는 작업이 필요한 경우 메모리에 부담을 덜어주기 위해 사용되는 공간입니다.

테이블스페이스 관련 쿼리

• 테이블스페이스 생성 쿼리

create tablespace ${테이블스페이스명}
datafile '/oracleData/project/member01.dbf' --파일경로
size 100M --초기 데이터 파일 크기 설정
autoextend on next 100M -- 초기 크기 공간을 모두 사용하는 경우 자동으로 파일의 크기가 커지는 기능
maxsize 1024M -- 데이터파일이 최대로 커질 수 있는 크기 지정 기본값 = unlimited
uniform size 1M -- EXTENT 한개의 크기를 설정
SEGMENT SPACE MANAGEMENT AUTO; -- 세그먼트 빈 공간 관리 방식을 ASSM 방식으로 설정

• 데이터 파일에 대한 설정 확인

SELECT * FROM DBA_DATA_FILES ORDER BY file_name;

• 테이블 스페이스 조회

-- 테이블별 테이블 스페이스 조회 쿼리
SELECT table_name, tablespace_name, partitioned FROM tabs;

-- 인덱스별 테이블 스페이스 조회 쿼리
SELECT index_name,table_name, tablespace_name FROM all_indexes ORDER BY tablespace_name;

• 테이블 스페이스 용량 조회

-- check tablespace usage
select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.maxmytes)/1024/1024,1) "TotalMB",
         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%",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.maxmytes)/1024/1024,1)*100,2) "MaxUsed%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt, 0 as maxmytes
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0 ,sum(maxbytes)
          from     dba_data_files
          group by tablespace_name
          ) a
group by a.tablespace_name
order by tablespace;

• 테이블스페이스 변경 쿼리

-- 테이블에 대한 테이블스페이스 변경 쿼리
ALTER TABLE 테이블명 MOVE TABLESPACE 테이블스페이스명;

-- 테이블스페이스에 대해 데이터파일 추가 쿼리
ALTER TABLESPACE DUMTS ADD DATAFILE '/oracleData/project/member02.dbf' SIZE 1G;
-- 데이터 파일 자동증가 설정
ALTER DATABASE DATAFILE '/oracleData/project/member02.dbf' AUTOEXTEND ON NEXT 100M;

-- INDEX에 대한 테이블스페이스 변경 쿼리
ALTER INDEX 인덱스명 REBUILD TABLESPACE 테이블스페이스명;

그래서 테이블스페이스를 왜 새로할당하는건데?

테이블스페이스를 테이블별로 혹은 정책에 따라 할당하는 이유는 다음과 같은 이유가 가장 큽니다.

• 백업, 롤백의 용이성
  만약에 기존 테이블스페이스에 모든 테이블에 대한정보를 기록하고 있었다면 롤백할 때 시간이 어마무시할 겁니다.
  하지만 테이블스페이스의 분리로 인해 중요한 테이블들만 롤백을 진행한다면 훨씬 효율적이겠죠

TMI

• 오라클 데이터파일의 MAX 사이즈는 32GB이다.
  • 따라서 자동으로 데이터파일 사이즈 늘리게 해두었어도 32GB 꽉차는 순간 장애가 발생합니다.
• 테이블 생성 시 따로 테이블스페이스 지정안하면 오라클에서 자동으로 테이블스페이스 할당해줌

느낀 점

테이블스페이스의 존재를 전혀 모르고 테이블을 관리하고 있었는데 용량 이슈랑 운영의 이유로 테이블 스페이스의 존재를 알아가다가 중요성을 느껴서 이번기회에 공부하였습니다.

MySQL도 테이블스페이스가 있는 것 같기는한데 안에 구조는 좀 다른느낌인 것 같더라고요.

나중에 DB 엔진, 스토리지 엔진도 따로 파서 공부해보고 싶습니다.
공부할 건 참 많네요 ㅋㅋㅋㅋ

참고

• https://myjamong.tistory.com/218
• https://pseonghoon.github.io/post/oracle-myth/