[Oracle] 오라클 테이블스페이스 생성,변경,추가,용량확인

오라클의 기본 테이블스페이스는 "USERS"이다.
하지만 오라클에서는 저장되는 데이터유형/관리목적/사용자별 로 테이블스페이스를 분리하는것을 추천한다.
유형별로 테이블스페이를 나눠서 관리한다면, 추후 데이터 백업/삭제할때, 관련된 테이블스페이스만 적용하면 되기때문이다.
(이것외에 몇가지 장점이 있다. 하지만 대규모가 아니라면, 굳이..라는 생각이들면서도 장기적으로 볼때 분리하는게 어느정도 맞는것같기도 하다.)

이번 프로젝트에서는 데이터유형별/관리별로 테이블스페이스를 나눴었다.
데이타타입이 일반/blob인경우를 먼저 나누고, 일반데이터타입에서 로그/결과/운영으로 또 나눳다.
그래서 총 blob데이터타입/운영파일/운영로그/운영결과 4개의 테이블스페이를 두었다.

 

 

테이블 스페이스 생성

- 데이터파일에 들어가는 .dbf는 추후 용량 추가를 위해, 파일명뒤에 01번호를 붙어주는것이 좋다.
- 추후 용량추가를 할때, '/경로/테이블스페이스파일명02.dbf'라고 뒤에 숫자만 증가시켜서 추가할 수 있기 때문이다.

CREATE TABLESPACE 테이블스페이스명 DATAFILE '/경로/테이블스페이스파일명01.dbf' SIZE 30G AUTOEXTEND ON NEXT 5M;
-- AUTOEXTEND ON NEXT 5M :  자동증가 용량

이미 생성한 테이블의 테이블스페이스 변경

데이터를 생성한 후 에 테이블스페이스를 변경하려면, 아래 쿼리로 변경할 수 있다.

alter table 테이블명 move tabledpace 테이블스페이스명;

 

하지만 blob일때,인덱스를 생성한 테이블 일때, 확실하게 변경이 되었는지 확인이 필요하다.
아래의 쿼리로, 해당테이블의 인덱스가 올바르게 테이블스페이스를 변경했는지 확인해야한다.

테이블 인덱스의 테이블스페이스 확인

select TABLE_NAME, INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME from user_indexes;

만약 올바르게 테이블스페이스를 변경하지 못했다면,아래 쿼리로 다시 변경해야한다.

ALTER TABLE 테이블명 MOVE 인덱스타입(인텍스명) STORE AS  (TABLESPACE 테이블스페이스명);

오라클 테이블 스페이스별 용량확인

해당 쿼리로, 각 스페이스별 사용용량을 확인 할 수 있다.

select   substr(a.tablespace_name,1,30) tablespace,
         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%"
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
group by a.tablespace_name
order by tablespace;

테이블스페이스에 해당하는 테이블 조회

SELECT TABLESPACE_NAME, TABLE_NAME 
FROM DBA_TABLES 
-- WHERE TABLESPACE_NAME  in ( '원하는 테이블스페이스명')
ORDER BY TABLESPACE_NAME, TABLE_NAME;

테이블스페이스 용량 추가

테이블스페이스의 데이터파일 확인

-- 해당 쿼리를 사용하여, 데이터파일의 번호 및 경로를 확인할 수 있다.
select name from v$datafile;

용량 추가

 -- 위 쿼리로 데이터파일의 번호 및 경로를 확인 후, 데이터파일+1, 하여 최대 30G까지 원하는 용량을 추가한다.
alter tablespace 테이블스페이스명 add datafile '/경로/테이블스페이스파일명02.dbf' size 30G autoextend on;

---

오라클을 사용해보면서, 오라클이 강력한 이유를 알게되었다, 관리/운영면에서 사용한다면 유지보수를 간단하게 할 수 있기때문이다. (어쩌면 유지보스에 용이하게 DB설계를 잘해서 그런걸수도..)