[Oracle] Tablespace and Data File

정성 가득한 글을 작성하신 원작자님께 감사드린다.

출처 : http://blog.naver.com/zoshstbt/50131874744

 

 

1. Tablespace 개념

• Database Buffer Cache는 여러개의 Tablespace로 구성된다.
• Server Process는 sql 쿼리를 처리하기 위해서 execute 단계에서 필요한 Table이 존재하는 Data File의 block을 해당 Tablespace에 복사한다.
• Tablespace는 하나이 상의 Data File을 갖을 수 있다.
• Tablespace에 클수록 Data File로부터 복사할 수 있는 block의 양이 많아 지기 때문에 Tablespace는 클수록 좋다.(메모리 용량의 제한)
• DBWR은 Database Buffer Cache로 부터 데이터를 Data File로 저장할 때 해당 Tablespace가 갖는 Data File에 분산 저장을 수행한다.
• 데이터를 나눠서 각각의 Data File에 저장한다.
• Data File의 용량이 부족할 경우 Data File의 용량을 증가시키는 것보다 Data File을 추가하는 것이 효율적이다.
• select 등의 scan 동작을 수행할 경우 용량이 큰 Data File을 전체 scan 하는 것은 처리속도가 저하된다.

Tablespace 관리

• Dictionary-managed tablespace (DMT)
• data dictionary에 의해서 free extent 공간이 관리된다.
• Data File의 block 관리를 dictionary를 통해서 한다.
• 한번에 많은 처리를 하는 경우 data dictionary 혼자서 모든 것을 처리해야 하기 때문에 부하가 발생된다.
• Oracle 8i version 까지 사용되었다.

• Locally managed tablespace (LMT)
• 각 tablespace에서 free extent 공간을 관리한다.
• Oracle 9i version 부터 기본값으로 설정된다.

Tablespace 생성

create tablespace test_space

datafile '/home/oracle/oradata/testdb/test01.dbf' size 10M

autoextend on next 5M maxsize 200M

segment space management auto;

• size 10M : /home/oracle/oradata/testdb/test01.dbf Data File의 size를 10M로 생성함
• UNIX 환경에서 Data File의 크기는 2G가 넘지 않도록 권장
• autoextend on : /home/oracle/oradata/testdb/test01.dbf Data File의 용량이 부족할 경우 자동적으로 용량이 증가되도록 설정
• next 5M : autoextend on 옵션으로 자동적으로 증가되는 단위가 5M
• maxsize 200M : autoextend on 옵션으로 자동적으로 증가되는 용량의 한계가 200M
• segment space management auto : 성능향상을 위한 옵션(10g에서는 기본값으로 설정됨)
• autoextend on ~ 문을 생략할 경우 생성된 Data File의 용량은 10M가 고정됨
• autoextend on 의 반대는 autoextend off

Tablespace 상태 변경

특정 tablespace를 읽기 전용으로 변경 및 복원할 수 있다.

• alter tablespace test_space read only;
• select, drop 문만 가능함
• alter tablespace test_space read write;

특정 tablespace를 offline/online 상태로 변경 시킬 수 있다.

• offline으로 상태를 변경하는 것은 해당 tablespace만을 shutdown 시키는 것과 같은 의미이다.
• alter tablespace test_space offline;
• alter tablespace test_space online;

• offline으로 상태를 변경할 수 없는 tablespace
• SYSTEM tablespace
• UNDO tablespace
• Default Temporary tablespace

Tablespace의 크기 변경

• Data File의 사이즈를 증가시키는 방법
• 자동 : tablespace 생성시 autoextend on ~ 옵션을 사용한 경우 자동으로 size 증가
• 수동 1 (고정 크기 변경)
• alter database
• datafile '/home/oracle/oradata/testdb/test01.dbf'
• resize 300M;
• 수동 2 (자동 증가로 변경)
• alter database
• datafile '/home/oracle/oradata/testdb/test01.dbf'
• autoextend on next 5M maxzie 300M;

• Data File의 추가하는 방법
• alter tablespace test_space
• add datafile '/home/oracle/oradata/testdb/test02.dbf' size 300M;

2. Tablespace의 종류 및 특징

2.1 SYSTEM tablespace

Data Dictionary를 저장하고 있으며, tablespace가 손상될 경우 Oracle 서버가 시작이 되지 않는다.

사람이 생성하거나 삭제할 수 없으며, tablespace를 offline 시킬 수 없다.

2.2 SYSAUX tablespace

Oracle 10g 부터 등장한 tablespace로 주로 Oracle 서버의 성능 튜닝을 위한 데이터들이 저장되어 있다.

사람이 생성하거나 삭제할 수 없다.

2.3 UNDO tablespace

사용자가 DML을 수행할 경우 원본 Data들을 저장해 두는 tablespace이다.

사용자가 생성할 수 있다. Undo에 관한 내용은 추후 포스팅 할 예정이다.

2.4 Temporary tablespace

임시자료를 저장하는 tablespace로 Database가 재시작되면 초기화 되기 때문에 저장용도로 사용될 수 없다.

• 오래저장되어야 하는 데이터들을 저장해서는 안된다.

일반적으로 정렬등의 작업을 수행하기 위해서 Server Process의 PGA 공간을 사용하는데, 해당 PGA의 공간이 부족한 경우 임시로 Temporary tablespace에서 정렬 작업을 수행한다.

• Temporary tablespace의 공간마져 부족한 경우 sql 처리가 에러가 나고 작업이 중지된다.

Temporary tablespace는 동시에 사용할 수 없기 때문에 성능향상을 위해서 사용자별로 하나씩 할당해주는 것이 좋다.

Temporary tablespace 생성

create temporary tablespace test_temp

tempfile '/home/oracle/oradata/testdb/test_temp01.dbf' size 500M;

• tempfile : Oracle 9i version 까지는 datafile 이라고 적어도 됐음

Default Temporary tablespace

SYSTEM tablespace와 temporary tablespace를 할당받지 못한 tablespace가 사용하는 것으로 기본적으로 1개는 있어야 한다.

생성된 Temporary tablespace가 여러개일 경우 Default Temporary tablespace를 변경하기 위해서는 다음과 같은 명령을 사용한다.

• alter database default temporary tablespace 이름;

3. Data File의 이동방법

Data File 이동시 순서

1. 옮겨질 Data File을 offline으로 변경
2. offline 불가능한 경우 shutdown
3. OS 명령어를 사용하여 Data File 복사 or 이동
4. alter database 명령으로 Data File의 위치 정보 변경 (mounted 상태)
5. alter database rename
6. file '/home/oracle/oradata/testdb/test01.dbf'
7. to '/home/oracle/oradata/test01.dbf';
8. online으로 상태 변경
9. shutdown 시킨 경우 OPEN 상태로 변경

4. Tablespace 정보 파일

Tablespace

• dba_tablespaces
• v$tablespace

Data file

• dba_data_files
• v$datafile

Temp file

• dba_temp_files
• v$tempfile

5. 명령어 정리

5.1 Data File 및 Tablespace 관련 조회

select tablespace_name, status, contents, extent_management, segment_space_management

from dba_tablespaces;

select tablespace_name, bytes, file_name

from dba_data_files;

5.2 Tablespace 생성

create tablespace <tablespace 명>

datafile '<data file명>' size <크기>

segement space management auto; <- 10g에서는 기본값으로 사용안해도 됨

[autoextend on next <크기> maxsize <크기>] <- 자동적으로 크기를 증가시키고자 할때 사용

create undo tablespace <tablespace 명>

datafile '<data file명>' size <크기>

create temporary tablespace <tablespace 명>

tempfile '<data file명>' size <크기>

5.3 Tablespace 확장

alter tablespace <tablespace 명>

add datafile '<data file명>' size <크기>

alter database datafile

'<data file명>' resize <크기>

5.4 Tablespace 관리

alter tablespace <tablespace 명> offline;

alter tablespace <tablespace 명> online;

offline 가능한 tablespace의 경우

alter tablespace <tablespace 명> rename

datafile '<원본 data file명>'

to '<data file명>'

offline 불가능한 tablespace의 경우

alter database rename

file '<원본 data file명>'

to '<data file명>'

5.5 Tablespace 삭제

drop tablespace <tablespace 명>

including contents and datafiles cascade constraints

5.6 각 data file의 실제 사용량 확인

select distinct d.file_id file#, d.tablespace_name ts_name, d.bytes/1024/1024 MB, d.bytes/8192 total_blocks, sum(e.blocks) used_blocks, to_char(nvl(round(sum(e.blocks)/(d.bytes/8192),4),0)*100,'09.99') || ' %' pct_used

from dba_extents e, dba_data_files d

where d.file_id = e.file_id(+)

group by d.file_id, d.tablespace_name, d.bytes

order by 1,2;

출처 : http://blog.naver.com/zoshstbt/50131874744