오라클 데이터베이스 관리자 기초- (6)테이블 스페이스 및 데이터 파일 관리

* 테이블 스페이스 및 데이터 파일

테이블 스페이스를 사용했을때 가장 큰 장점

1. 데이터 용량에 대한 제한이 없음.(물리적인 제약이 없음). 만약 테이블 스페이스를 사용하지 않는다면 대용량 데이터베이스라고 부를 수 없다. 

2. 외부 하드 디스크에 접속이가능하기 때문에 성능이 우수(?)

 

특징

- 데이터 딕셔너리가 저장되어있음

- 사용자는 데이터파일을 지정 할 수 없음

- tablespace는 데이터파일로 구성되어 있음

- 데이터파일은 tablespace에 무조건 저장되어있어야함

- Oracle에서 모든 파일들은 tablespace로 묶어서 저장되어있음

- segment도 테이블 스페이스에 저장된다. 

- 한 개 이상의 데이터 파일로 테이블 스페이스 구성. 

 

테이블 스페이스 유형

1. SYSTEM 테이블 스페이스

- 데이터 베이스와 함께 생성. 직접 만들 수 없다.

- 데이터 딕셔너리 포함. 일반 관리자는 절대 직접 사용하면 안되고 빈 공간만 10% 이상으로 유지되도록 확인만 한다

 

2. non-SYSTEM 테이블 스페이스(SYSTEM 테이블 스페이스가 아닌 나머지 전부 다)

- 총 3가지 종류가 존재

-> 1. 유저용 테이블 스페이스 : 말 그대로 일반 사용자가 사용하는 테이블 스페이스

-> 2. temporary(임시) 테이블 스페이스 : 정렬하기 위해 임시로 저장하는 테이블 스페이스

-> 3. undo 테이블 스페이스

 

테이블 스페이스의 영역 관리

1. 지역적 관리 테이블 스페이스

확장 영역을 비트맵을 통해 테이블 스페이스 내에서 관리.

딕셔너리 관리 테이블 스페이스의 단점을 전부 개선했기때문에 지역적 관리 테이블 스페이스를 대부분 사용.

 

2.  딕셔너리 관리 테이블 스페이스

확장 영역을 데이터 딕셔너리가 관리하기때문에 트랜잭션이 발생.

초기에는 딕셔너리 관리 테이블 스페이스만 존재했으나 문제가 많아서 지역적으로 관리되는 테이블 스페이스를 개발

 

사용 가능한 확장 영역(free extent)

예를들어, usr table space 의 usr01.dbf라는 dbf파일을 만들때 첫 블록은 bitmap으로 어떤 블록이 공간을 사용하는지 정보를 저장한다. 

테이블을 만들때 사용 가능한 영역을 받아오는 공간을 extent라 하는데 하이워터마크가 찍힌 곳 까지만 데이터를 저장할 수 있고, 부족할 경우 블록을 더 받아온다.  하이워터마크는 절대로 앞으로 돌아가는 경우가 없기때문에 full scan을 진행하면 블록에 빈공간이 엄청 많아도 다 읽어와야 한다. 

 

테이블 생성

1. 지역적으로 관리되는 테이블 스페이스

CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M

// userdata : 생성할 테이블 스페이스의 이름

DATAFILE '/u01/oradata/userdata01.dbf' : 테이블 스페이스를 구성하는 데이터 파일의 경로.

exent는 따로 써주지 않는다.

 

2. undo 테이블 스페이스 생성

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;

 

3. temporary 테이블 스페이스 생성

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M

// extent는 따로 써주지 않는다. 테이블 스페이스를 지정할때 다른 테이블 스페이스들과 달리 tempfile로 지정하기 때문에 검색할때 경로도 다르다.

 

테이블 스페이스의 크기조정 방법

- AUTOEXTEND를 사용한 자동 변경(데이터 파일을 늘림)

- ALTER TABLE SPACE를 사용한 수동 변경(데이터 파일을 늘림)

- ADD DATAFILED을 사용해 데이터 파일 추가

 

데이터 파일의 자동 확장 활성화

– CREATE DATABASE
– CREATE TABLESPACE
– ALTER TABLESPACE … ADD DATAFILE

위의 3가지 명령으로 데이터 파일의 생성될때 AUTOEXTEND ON를 사용한다.

 

수동으로 데이터 파일 크기 조정

resize 명령으로 데이터를 줄이는 것은 이론적으로 가능하지만 실무에서 데이터를 줄이는 경우는 없기때문에 확장할때만 사용한다고 생각하면 좋다.

 

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

데이터 파일을 추가해서 할당된 공간 확장. 하드 드라이브가 추가됐을때만 사용한다.

 

table space 조회

[실습]

SELECT tablespace_name, status, contents,
extent_management, segment_space_management
FROM dba_tablespaces;

- Tablespace의 상태를 조회한다.
- STATUS : 사용 가능여부
- CONTENTS : 저장 Segment의 종류
- EXTENT_MANAGEMENT : Extent의 할당 및 관리 방식
- SEGMENT_SPACE_MANAGEMENT : Block내의 공간 관리 방식

SELECT tablespace_name, bytes, file_name
FROM dba_data_files;

- 데이터 딕셔너리에서 조회

- open 상태에서만 조회 가능 

SELECT t.name tablespace_name, d.bytes, d.name file_name
FROM v$tablespace t, v$datafile d 
WHERE t.ts#=d.ts#;

- 동적 성능 테이블에서 조회 

- mount 상태 이상에서 조회 가능

 

테이블 스페이스 생성

<명령어 종류>

CREATE TABLESPACE <tablespace명>
DATAFILE '' SIZE <크기>;

- 오라클 10g, 11g, 12c 버전에서 사용자용 tablespace생성
- Extent management 는 locally 방식으로, segment space management는 AUTO 방식으로 생성된다.

 

[실습]

CREATE TABLESPACE insa 
DATAFILE '/app/ora12c/oradata/DB12/insa01.dbf' SIZE 1M;

-> 테이블 스페이스 insa라는 이름을 가지는 테이블 생성 명령

 

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

-> 생성된 테이블 스페이스 설정과 데이터 파일 확인 가능 

 

테이블 스페이스 확장-수동

<명령어 종류>

ALTER DATABASE DATAFILE
'<data 파일>' RESIZE <크기>;
- 지정된 파일의 크기를 늘린다.

 

ALTER TABLESPACE <tablespace명>
ADD DATAFILE '<추가될 data file>' SIZE <크기>;
- Data file을 추가한다

 

[실습]

ALTER DATABASE DATAFILE
'/app/ora12c/oradata/DB12/insa01.dbf' RESIZE 2M;

-> insa 데이터 파일의 크기를 1m에서 2m으로 확장

 

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

-> 생성된 테이블 스페이스 설정과 데이터 파일 확인 가능

ALTER TABLESPACE insa 
ADD DATAFILE '/app/ora12c/oradata/DB12/insa02.dbf' SIZE 2M;

-> tablespace에 2M 크기의 data file을 추가 한다. 

 

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

-> 생성된 테이블 스페이스 설정과 데이터 파일 확인 가능

 

테이블 스페이스 확장-자동

<명령어 종류>

CREATE TABLESPACE <tablespace명>
DATAFILE '' SIZE <크기>
AUTOEXTEND ON NEXT <크기> MAX SIZE <크기>;
-> 자동으로 커지는 data file을 갖는 tablespace를 생성한다.
-> NEXT : 증가치
->  MAX SIZE : 최대 크기

 

ALTER TABLESPACE <tablespace명>
ADD DATAFILE '<추가될 data file명>' SIZE <크기>
AUTOEXTEND ON NEXT <크기> MAX SIZE <크기>;
-> tablespace에 자동으로 커지는 data file을 추가 한다.

 

ALTER DATABASE
DATAFILE '<변경할 data file명>
AUTOEXTEND [ON | OFF] NEXT <크기> MAX SIZE <크기>;
-> 지정한 data file을 자동증가를 설정한다.

 

[실습]

CREATE TABLESPACE usr

DATAFILE
'/app/ora12c/oradata/DB12/usr01.dbf' SIZE 2M AUTOEXTEND ON,
'/app/ora12c/oradata/DB12/usr02.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M,
'/app/ora12c/oradata/DB12/usr03.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M MAXSIZE 20M;

-> 각각 설정이 서로 다른 세 개의 data file을 추가 한다. 크기가 자동으로 증가하는 autoextend 설정은 data file 별로 따로 설정한다. 

 

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

-> 생성된 테이블 스페이스 설정과 데이터 파일 확인 가능

SELECT tablespace_name, file_name, autoextensible, increment_by, maxbytes
FROM dba_data_files
WHERE tablespace_name = 'USR';

ALTER TABLESPACE insa
ADD DATAFILE '/app/ora12c/oradata/DB12/insa03.dbf' SIZE 2M
AUTOEXTEND ON NEXT 5M MAXSIZE 20M;

-> data file의 자동 증가는 data file 각각의 설정이므로 기존 tablespace에 자유롭게 추가할 수 있다.

 

SELECT tablespace_name, file_name, autoextensible, increment_by, maxbytes
FROM dba_data_files
WHERE lower(tablespace_name) = 'insa';

 

ALTER DATABASE DATAFILE '/app/ora12c/oradata/DB12/insa01.dbf'
AUTOEXTEND ON;

 

SELECT tablespace_name, file_name, autoextensible, increment_by, maxbytes 
FROM dba_data_files 
WHERE lower(tablespace_name) = 'insa';

 

autoextensible : 자동 증가

increment_by : 시퀀스가 증가되는 단위

 

테이블 스페이스 삭제

- 테이블 내에 내용이 있으면 삭제가 되지 않을때, 그럴때는 including contents 명령을 사용해야 삭제가 가능하다.

- cascade constraints는 모든 참조 무결성 제약 조건을 삭제한다. 

- system 테이블 스페이스, undo 테이블 스페이스, temporary 테이블 스페이스는 삭제할 수 없다. 즉 유저용 테이블 스페이스만 삭제 가능. 

 

DROP TABLESPACE 
[INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS];
- 지정된 tablespace를 삭제한다.
- INCLUDING CONTENT : Tablespace에 segment가 존재할 때 segment를 같이 삭제한다.
- CASCADE CONSTRAINTS : 삭제되는 tablespace의 table을 다른 tablespace의 table이 참조하는 경우에 해당 constraint를 같이 삭제한다.
- AND DATAFILES : Tablespace에 포함된 data file을 같이 지운다. 이 옵션을 쓰지 않으면 데이터 파일은 OS상에서 직접 지워야한다.