[Oracle] Tablespace 테이블스페이스 총 정리(종류, 생성, 삭제, 조회, 변경)

Oracle Tablespace

일반적으로 데이터베이스를 배운다고 했을때 SQL문장을 사용하는 방법부터 배웁니다. 데이터를 조회, 수정, 삭제 작업을 하면서 문들 이런 생각이 들 수 있습니다. "내가 조회하는 이 데이터들은 도대체 어디에 저장 되어 있는거지?" 데이터를 조작했으니 어딘가에는 물리적으로 존재해야한다는 것을 인식하게 됩니다. Oracle에서는 Data file 이라는 물리적 파일 형태 저장하고 이러한 Data file이 하나 이상 모여서 Tablespace라는 논리적 저장공간을 형성합니다.

 

Tablespace는 하나의 데이터베이스 안에 가장 큰 논리적 저장공간으로 업무의 단위나 사용용도에 따라 여러개의 Tablespace로 분리하여 관리되고 Segment(오브젝트)라는 논리적 저장공간의 집합이기도 합니다.

 

 

Tablespace 종류

 Tablespace의 종류는 크게 3가지로 나뉘고 데이터베이스 생성 시 꼭 필요한 Tablespace 4개가 있습니다.

 

 

Permanent Tablespace

영구 테이블스페이스는 가장 일반적인 테이블스페이스로 데이터를 축적용도로 사용되는 공간입니다. Undo와 Temporary와는 다르게 고의적으로 삭제하지 않는한 영구적으로 보존되는 객체들을 저장하기 위한 용도입니다. USERS나 EXAMPLES 테이블스페이스처럼 임의의 이름을 지정하여 원하는 데이터를 저장할 수 있고 데이터베이스가 운영되기 위해 꼭 필요한 SYSTEM과 SYSAUX 테이블스페이스가 있습니다.

 

SYSTEM(필수 요소)

데이터베이스의 운영에 필요한 기본 정보를 담고 있는 Data Dictionary Table이 저장되는 공간으로 데이터베이스에서 가장 중요한 Tablespace 입니다. 중요한 데이터가 담겨져 있는 만큼 문제가 생길 경우 자동으로 데이터베이스는 종료될 수 있고 일반 사용자들의 오브젝트들을 저장하지 않는 것을 권장합니다. 혹여나 사용자들의 오브젝트에 문제가 생겨 데이터베이스가 종료되거나 완벽한 복구가 불가능한 상황이 발생할 수 있습니다.

 

SYSAUX(필수 요소)

SYSAUX Tablespace는 SYSTEM Tablespace의 보조로 기존에 SYSTEM Tablespace에 있는 다양한 유틸리티 및 기능들 분리하여 저장한 공간입니다. SYSTEM과 마찬가지로 데이터베이스 운영에 필수적으로 있어야하는 Tablespace입니다. SYSAUX Tablespace에 문제가 생길 경우 시스템상에는 별 다른 문제가 없지만, SYSAUX Tablespace에 저장되어 있는 요소들의 기능들은 사용할 수 없게됩니다. 대표적으로 AWR(Auto Workload Repository)기능이 있습니다.

 

 

Undo Tablespace(필수 요소)

읽기 일관성을 유지하기 위해 사용되는 Tablespace입니다. 데이터베이스 운영 중 많은 사용자들에게서 DML 작업이 이루어집니다. 이때 Rollback하게 되는 경우를 대비하여 DML 작업이 발생했을 때 수정 이전의 값에 대한 정보를 UNDO Segment에 저장합니다. 이러한 Undo Segment에 대한 관리 공간으로 Undo Tablespace를 사용하게되고 데이터베이스 운영에 있어서 필수적으로 적어도 하나의 Undo Tablespace가 필요합니다.

 

 

Temporary Tablespace(필수 요소)

Temporary Tablespace도 필수적으로 있어야합니다. 사용자 쿼리의 요청으로 정렬하는 작업이 필요한 경우 메모리에 부담을 덜어주기 위해 사용되는 공간입니다.

 

 

Tablespace 종류 확인

SYS@orcl> select tablespace_name, contents from dba_tablespaces;

 

 

Tablespace 실습

실습환경

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1

 

Tablespace 생성에 대한 옵션은 정말 많습니다. 그 중에서 가장 많이 사용되는 옵션들을 이번 글에서 실습해볼려고 합니다. 더 자세한 내용은 아래 Oracle Document링크를 통해 확인 가능합니다.

CREATE TABLESPACE

 

Create Permanent Tablespace

CREATE
[BIGFILE | SMALLFILE(기본값)]
TABLESPACE <테이블스페이스명>
DATAFILE '<경로>' SIZE <크기>
[EXTENT MANAGEMENT
	[
    	DICTIONARY | LOCAL(기본값) [AUTOALLOCATE(기본값) | UNIFORM SIZE <크기>]
    ]
]
[SEGMENT SPACE MANAGEMENT [AUTO(기본값) | MANUAL]]

Tablespace 생성에 있어서 기본적으로 꼭 알아야할 옵션들입니다.

 

 

기본 생성

SYS@orcl> create tablespace abc
datafile '/opt/oracle/dbf/abc.dbf' size 50m;

SYS@orcl> select
    tablespace_name
    ,contents
    ,extent_management
    ,allocation_type
    ,segment_space_management
    ,bigfile 
from dba_tablespaces 
where tablespace_name = 'ABC'

필수적인 정보만 입력하여 Tablespace를 생성해봤습니다. 종류는 PERMANENT이고 다른 옵션은 기입하지 않아 SMALL FILE로 EXTENT_MANAGEMENT는 LOCAL 그리고 SEGMENT_SPACE_MANAGEMENT는 AUTO 형태로 저장되었습니다.

 

BIGFILE

SQL@orcl> create bigfile tablespace abc
datafile '/opt/oracle/dbf/abc.dbf' size 100m;

SYS@orcl> select
    tablespace_name
    ,contents
    ,extent_management
    ,allocation_type
    ,segment_space_management
    ,bigfile 
from dba_tablespaces 
where tablespace_name = 'ABC'

BIGFILE과 SMALL FILE의 가장 큰 차이는 만들 수 있는 데이터파일의 갯수와 크기 입니다. BIGFILE의 경우 데이터파일은 딱 하나를 사용할 수 있습니다. BIGFILE은 ASM(Automatic Storage Management)이 생기면서 만들어졌습니다. BIGFILE이 나오기 전에 SMALLFILE은 여러개의 디스크에 균등하게 수동으로 데이터파일을 만들어 줬습니다. ASM을 사용할 수 있으면 자동으로 균등하게 디스크별로 공간을 할당해주기 때문에 굳이 여러개의 데이터파일의 관리할 필요 없이 하나의 파일을 사용하고 ASM으로 공간을 할당하면 더 쉽게 관리할 수 있어 BIGFILE을 사용합니다.

 

 

EXTENT MANAGEMENT

Tablespace의 공간 할당은 Extent 단위로 진행됩니다. DML 작업이 계속 반복되면서 Extent의 할당과 반환이 발생하는데 어느 Extent를 사용해도 되는지에 대한 정보 관리가 필요합니다. 이러한 EXTENT MANAGEMENT에는 DICTIONARY와 LOCAL 방법이 있습니다.

 

DICTIONARY(구식)

사용가능한 Extent에 대한 정보를 Data Dictionary에서 관리하는 방법입니다. 이방법은 각 Segment마다 다른 Extent 크기를 설정할 수 있지만, 쿼리 작업이나 DML작업을 할때만으로도 내부적으로 많은 양의 Data Dictionary 조회 작업이 발생하는데 Extent 관리까지 하게되면 Data Dictionary에 대한 경합발생 가능성이 높아 현재는 사용되지 않는 방식입니다.

 

LOCAL

Data File의 헤더에 비트맵을 통해 Extent의 사용 유무를 관리하는 방식입니다. 각 Datafile에 비트맵을 사용하여 Resource의 사용량이 높아지지만 Data Dictionary Table 처럼 중요한 오브젝트의 경합을 줄이는 것이 더 중요하다. 기본값인 AUTOALLOCATE 방식을 사용하면 자동으로 Extent의 크기를 정하도록 위임 가능하고 UNIFORM 옵션을 사용하면 모든 Extent의 크기를 동일하게 설정 가능합니다.

 

SYS@orcl> create tablespace abc
datafile '/opt/oracle/dbf/abc.dbf' size 20m
extent management local uniform size 1m;

SYS@orcl> select 
    tablespace_name
    ,initial_extent
    ,next_extent
    ,extent_management
    ,allocation_type
    ,segment_space_management 
from dba_tablespaces
where tablespace_name = 'ABC';

첫번째 Extent와 다음 Extent의 크기 모두 1MB인것이 확인되고 ALLOCATION_TYPE이 UNIFORM으로 변경된 것을 확인할 수 있습니다.

 

 

SEGMENT SPACE MANAGEMENT

Segment의 공간 관리에 대한 옵션입니다. 쉽게 생각해서 테이블의 공간 관리를 어떻게 할것인가...를 묻는 옵션입니다. 현재는 AUTO 방식을 사용하고 있습니다.

 

MANUAL(구식)

Manual 방법은 Freelist를 사용해서 Insert가 가능한 블럭을 확인할 수 있습니다. 테이블 생성시 블럭이 일정 백분위 이하로 사이즈가 줄어들면 다시 사용가능한 블럭으로 Freelist에 등록하는 PCTUSED라는 기준을 정해주는 옵션이 있습니다. 그런데 이 PCTUSED 설정으로 인해 공간이 있음에도 불구하고 Extent를 계속 만들어야하는 상황이 발생합니다.

 

각 블록에 나름 골고루 데이터가 분포되어 모두 설정한 PCTUSED 이하로 안내려오는 경우가 발생할 수 있습니다. 이런 경우 사용가능한 블럭이 없는것으로 Freelist에서 인식되어 새로운 Extent를 할당해야하는 상황이 발생할 수 있습니다. 이런 문제를 해결하기 위해 AUTO 방식을 사용합니다.

 

AUTO

비트맵을 이용해서 비어있는 블록을 확인하는 방법입니다. PCTUSED 옵션이 사라지고 아래와 같이 비어있는 공간을 나타냅니다.

fs1 0 ~ 25%
fs2 25 ~ 50%
fs3 50 ~ 75%
fs4 75 ~ 100%
full INSERT X
never used O

4개 등급으로 나누고 총 6가지 상태를 나타내는 Bitmap 블럭을 사용하여  segment를 관리합니다. 이러한 방법을 ASSM(Automatic Space Segment Management)라고 합니다.

 

 

Create Temporary Tablespace

CREATE
[BIGFILE | SMALLFILE(기본값)]
TEMPORARY TABLESPACE <테이블스페이스명>
TEMPFILE '<경로>' SIZE <크기>
[EXTENT MANAGEMENT
	[
    	DICTIONARY | LOCAL(기본값) [UNIFORM SIZE <크기>(기본값 1M)]]
    ]
]

Temporary Tablespace는 기본적은로 LOCAL UNIFORM SIZE 1M 이고 SEGMENT SPACE MANAGEMENT는 AUTO를 사용할 수 없습니다.

 

SYS@orcl> create temporary tablespace abc
tempfile '/opt/oracle/dbf/abc.tmp' size 20m;

SYS@orcl> select 
    tablespace_name
    ,contents
    ,initial_extent
    ,next_extent
    ,extent_management
    ,allocation_type
    ,segment_space_management 
from dba_tablespaces
where tablespace_name = 'ABC';

 

 

Default Temporary Tablespace 변경

select * from database_properties where property_name like 'DEFAULT_TEMP%'

 

alter database default temporary tables=<테이블스페이스명>

위명령으로 변경 가능합니다.

 

 

Create Undo Tablespace

CREATE
[BIGFILE | SMALLFILE(기본값)]
UNDO TABLESPACE <테이블스페이스명>
DATAFILE '<경로>' SIZE <크기>
[EXTENT MANAGEMENT
	[
    	DICTIONARY | LOCAL(기본값) [AUTOALLOCATE(기본값)]
    ]
]

Undo Tablespace는 UNIFORM SIZE를 지정할 수 없고 Segment Space Management도 MANUAL만 가능합니다.

 

SYS@orcl> create undo tablespace abc
datafile '/opt/oracle/dbf/abc.dbf' size 20m;

SYS@orcl> select 
    tablespace_name
    ,contents
    ,initial_extent
    ,next_extent
    ,extent_management
    ,allocation_type
    ,segment_space_management 
from dba_tablespaces
where tablespace_name = 'ABC';

 

 

Default Undo Tablespace 변경

SYS@orcl> show parameter undo_tablespace

 

SYS@orcl> alter system set undo_tablespace=<테이블스페이스명>

위 명령으로 변경 가능합니다.

 

 

Tablespace Datafile 추가, 삭제

추가

SYS@orcl> alter tablespace users add datafile '/opt/oracle/app/oradata/orcl/users02.dbf' size 10m;

select 
    tablespace_name
    ,file_name 
from dba_data_files 
where tablespace_name = 'USERS';

 

 

삭제

select tablespace_name, file_id, file_name from dba_data_files where tablespace_name = 'USERS';

SYS@orcl> alter tablespace users drop datafile 6;

file id 혹은 file_name 전체 경로로 삭제 가능합니다.

 

 

Tablespace 삭제

drop tablespace <테이블스페이스명> including contents and datafiles;

contents : 모든 세그먼트를 삭제

datafiles : 모든 데이터파일까지 삭제