오라클 Admin (Tablespace)

1. Tablespace 및 Datafiles
가. 테이블스페이스 : 오직 1개 데이터베이스에만 소속될 수 있음. 최소 1개 이상의 datafile 로 구성, 논리적인 구성단위
나. 데이터파일 : 오직 1개 테이블스페이스, 데이터베이스에만 소속될 수 있음, 물리적인 구성단위

2. 테이블스페이스의 구분
가. System tablespace : 데이터베이스에 의해 생성, Data Dictionary 포함, System undo segment 포함
나. Non-System tablespace : DBA에 의해 생성하고, 삭제될 수 있는 테이블스페이스

3. 테이블스페이스 생성

CREATE TABLESPACE tablespace_name
DATAFILE 'datafile_location_and_filename' SIZE 10M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;


CREATE TABLESPACE tablespace_name
DATAFILE 'datafile_location_and_filename1' SIZE 10M AUTOEXTEND OFF
             , 'datafile_location_and_filename2' SIZE 10M AUTOEXTEND OFF
             , 'datafile_location_and_filename3' SIZE 10M AUTOEXTEND OFF;

참고 : 파일 1개당 최대허용 크기
– 32bit oracle : 16G
– 64bit oracle : 32G

리눅스에서 권장하는 파일 1개당 최대크기는 2G

4. 테이블스페이스 공간관리 방법
가. LMT(Locally Managed Tablespace) : 각 테이블스페이스에서 직접 free extents 를 관리, 각 테이블스페이스로 부하가 분산되어 DMT에 비해 상대적으로 성능상의 이점이 있다. 9i부터 기본옵션
나. DMT(Dictionary-Managed Tablespace) : free extents를 data dictionary에서 중앙관리, 사용량이 몰릴 때 성능저하가 생긴다. 8i까지 사용

5. Undo Tablespace
가. 오직 Undo segments만을 저장
나. extents 관리 방식은 LMT방식 이용

CREATE undo TABLESPACE undo1
DATAFILE 'datafile_location_and_filename' SIZE 40M;

-- 디폴트 언두 테이블스페이스 변경
-- pfile을 사용하는 경우 pfile도 수정해주어야 한다.
alter system set undo_tablespace=undo1;

6. Temporary Tablespaces
가. 정렬을 하기 위해 사용
나. 영구적인 데이터 저장은 불가능

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'location_and_filename' SIZE 500M;

ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE temp;

참고 : 데이터 정렬과 TEMPORARY TABLE

– ORDER BY, GROUP BY 등의 SQL문을 처리할 경우 서버프로세스는 PGA 영역에서 정렬을 처리한다. 단, PGA에서 처리 불가능한 크기일 경우 TEMPORARY TABLE을 이용하게 된다. 만약 정렬해야 하는 데이터 건수가 너무 많아 TEMPORARY TABLE에서도 처리할 수 없는 경우, 오라클은 에러메시지를 띄우며 정렬을 중단한다. 이 때문에 TEMPORARY TABLE의 크기결정은 매우 중요한 부분이다.

– 또한 Temporary Table은 하나의 인스턴스에 여러 개 만들수 있으므로, 성능향상을 위해 각 사용자마다 Temporary 테이블을 하나씩 할당해주는 것이 가장 이상적이다.

– 별도로 TEMPORARY 테이블스페이스를 지정해주지 않으면 DEFAULT 테이블스페이스가 할당된다.

7. Default Temporary Tablespace
가. Default Temporary Tablespace는 최소 1개 이상 있어야한다.
나. OFFLINE 모드로 변경할 수 없다.

alter database default temporary tablespace tablespace_name

8. Read Only Tablespace
가. DML 작업은 할 수 없는 읽기전용 테이블스페이스
나. 객체(object)는 삭제할 수 있다.

 ALTER TABLESPACE userdata READ ONLY;

 ALTER TABLESPACE userdate READ WRITE;

9. 테이블스페이스 OFFLINE
가. 테이블의 데이터를 더 이상 액세스 할 수 없음
나. OFFLINE 시킬 수 없는 테이블스페이스
– SYSTEM TABLESPACE
– 현재 사용중인 테이블 스페이스(undo segment)
– DEFAULT TEMPORARY TABLESPACE

 ALTER TABLESPACE userdata offline;

 ALTER TABLESPACE userdata online;

10. Tablespace의 크기변경 방법
가. DATAFILE의 크기 변경(자동, 수동)

ALTER DATABASE DATAFILE 'location_and_filename' RESIZE 200M;

나. DATAFILE의 추가

ALTER TABLESPACE tablespace_name
ADD DATAFILE 'location_and_filename' SIZE 200M;

참고 : TABLESPACE의 크기변경 시 datafile의 크기를 변경하는 방법보다 datafile을 추가하는 방법이 이론적으로는 성능에서 뛰어나다. 왜냐하면 n개의 데이터파일을 가진 테이블스페이스의 경우 데이터 저장시 1/n으로 분산저장하기 때문이다.

예) 3개의 datafile로 구성된 테이블 스페이스에 90M의 데이터가 저장 될 경우 각각의 datafile에 30M씩 저장된다.

11. Datafile의 이동
가. 이동순서
– 해당 파일의 사용금지 상태로 변경(offline, shutdown)
– OS 명령어로 해당 파일 복사/이동
– 옮긴 파일의 위치경로 수정(ALTER DATABASE …, ALTER TABLESPACE ….)
– 옮긴 파일의 사용가능 상태로 변경(ONLINE 등)

ALTER TABLESPACE tablespace_name offline;

!cp source_filename destination_filename

ALTER TABLESPACE tablespace_name
RENAME DATAFILE 'source_filename' TO 'destination_filename';

ALTER TABLESPACE tablespace_name online;

나. SYSTEM TABLESPACE, DEFAULT TEMPORARY TABLESPACE 등의 OFFLINE 처리가 불가능한 테이블 스페이스의 경우 서버를 shutdown 한뒤에 파일을 이동한다.

shutdown IMMEDIATE

!cp source_filename destination_filename

ALTER DATABASE RENAME
FILE 'source_filename' TO 'destination_filename';

ALTER DATABASE open;

12. TABLESPACE의 삭제
가. 삭제불가능 테이블스페이스 : 현재 사용중인 테이블스페이스, SYSTEM 테이블스페이스

DROP TABLESPACE tablespace_name
INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

참고 :
– INCLUDING CONTENTS AND DATAFILES : segments 및 datafile까지 삭제(물리적인 파일까지 삭제됨)
– CASCADE CONSTRAINTS : 연결된 제약조건까지 삭제