tablespace에 대한 고찰!!

tablespace에 대한 잡담




tablespace는 모든 데이터를 저장하고 있는 datafile의 논리적 명칭이다. 여기에는 table과 DML문을 수행해 insert된 data를 비롯, 유저 스키마 정보등 흔히 우리가 data라 부르는 모든 것들이 저장되는 영역이다.

 tablespace의 용도에 따라 시스템 tablespace와 비시스템 tablespace로 나눌 수 있으며, 시스템 tablespace에는 시스템 운영에 필요한 데이터 딕셔너리 데이터(메타데이터)가 저장된다. 그리고 10g 이후 시스템 tablespace에 SYSAUX tablespace가 새롭게 추가 되었고, 이 SYSAUX tablespace는 10g 이전의 SYSTEM과 여러 tablespace에서 담당하던 역할을 분담하는 역할을 한다. 그렇기에 시스템 tablespace라고 하면 SYSTEM, SYSAUX 두가지를 생각하면 될 것이다.

 비시스템 tablespace에는 TEMP(임시) tablespace, UNDO tablespace, 일반 tablespace가 있다.
TEMP tablespace는 order by절 등 정렬 작업을 요구하는 SQL문이 수행 될 때 PGA에 할당된 공간이 부족하면, TEMP tablespace에서 swap형식으로 내렸다 올렸다 하며 정렬한다. 이와같은 정렬 작업 때문에 index 생성시에도 TEMP tablespace가 사용된다. 정렬 작업에 TEMP tablespace가 사용되게 되면 메모리에서 해결하지 못하고 디스크까지 사용 한다는 의미이기 때문에 속도(성능)이 상당히 느려진다.
UNDO tablespace는 말그대로 rollback과 commit를 담당하는 undo segment를 저장하는 공간이다.
일반 tablespace는 우리가 평상시에 사용하는 data들이 저장되는 공간이다.(필자의 default tablespace는 USERS로 되어 있어,
table 생성할 때 tablespace의 이름을 명시하지 않는다면, table이 USERS tablespace로 저장된다)

extent 관리 방법에 따라서는 DMT(Dictionary Management Tablespace), LMT(Locally Management Tablespace)로 나눌 수 있으며, extent 정보를 딕셔너리 테이블에서 관리하는 DMT방식은 거의 쓰이지 않는다. 대부분이 datafile헤더 부분의 bitmap 정보를 활용, extent정보를 자체 해결하는 LMT방식을 사용한다. 이 LMT방식을 사용 해야만 자동 세그먼트 공간 관리 방식인 ASSM(Automatic Segment Space Management)를 이용 할 수 있다.

segment 공간 관리 방식에 따른 구분은 앞에서도 언급한 ASSM 방식과 PCTUSED, FREELIST option을 이용해 관리하는 수동 세그먼트 공간 관리 방식이 있다. 수동 세그먼트 공간 관리 방식 역시 관리하기 어려우므로 자주 사용되지 않는다.


1. tablespace(permenant - 평상시 쓰는 일반 사용자 tablespace)생성, 확장, 이동(open, mount상태에서), 삭제

우선 어떤 tablespace가 있는지, 용도와 상태, 익스텐트 관리 방법(LOCAL,DICT)등을 알아보자.

SQL> select tablespace_name, status, contents,
         extent_management, segment_space_management
         from dba_tablespaces;

tablespace가 어떤 datafile에 위치 해 있는지 알아보자.

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_data_files;

이제 tablespace를 만들어 보자. tablespace 이름은 MYTB, datafile 파일 이름은 mytb01.dbf이다. 

SQL> create tablespace MYTB
         datafile '/home/oracle/tablespace/mytb01.dbf' size 10m
         [segment space management auto] - (10g default);

만들어 졌는지 확인 해 보자.

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_data_files;

새로만든 tablespace의 size를 늘려보자. 원래는 10m. 20m로 늘릴 것이다.
그 후 확인 해 보자.

SQL> alter database datafile
         '/home/oracle/tablespace/mytb01.dbf' resize 20m;

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_data_files;

tablespace의 size를 늘리는 것 말고 같은 tablespace에 datafile을 추가시켜 tablespace의 용량을 늘릴 수도 있다.
tablespace에 datafie을 하나 추가 해보자. datafile명은 mytb02.dbf이며 20m를 할당 할 것이다.
그리고 확인 해 보자.

SQL> alter database MYTB
         add datafile '/home/oracle/tablespace/mytb02.dbf' size 20m;

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_data_files;

OPEN 상태에서 tablespace의 datafile을 이동 해 보자. (system, sysaux tablespace는 OPEN 상태에서 이동 불가능)
먼저 MYTB tablespace를 offline 상태로 바꾸어야 한다.

SQL> alter tablespace MYTB offline;

디렉토리를 하나 만들고, 그곳으로 mytb01.dbf 파일을 이동 시킨다.
SQL> !mkdir /home/oracle/tablespace/mytb
SQL> !mv /home/oracle/tablespace/mytb01.dbf /home/oracle/tablespace/mytb/mytb01.dbf

그 후 이동 경로를 Database에 알려 주자.

SQL> alter tablespace mytb rename
         datafile '/home/oracle/tablespace/mytb01.dbf'
         to '/home/oracle/tablespace/mytb/mytb01.dbf';

그리고 확인 해 보자.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
         from dba_data_files;

이번에는 MOUNT 상태에서 tablespace의 datafile을 이동 해 보자. (모든 tablespace 가능 - system, sysaux tablespace는 반드시 mount단계에서 해야 한다. 필자는 system tablespace - system01.dbf 파일을 이동 할 것이다.)

우선 DB를 shutdown 시킨다.

SQL> shutdown immediate

그 후 디렉토리를 만들고 그 경로에 system01.dbf 파일을 이동한다.

SQL> !mkdir -p /app/oradata/tablespace/system
SQL> !mv /app/oradata/testdb/system01.dbf /app/oradata/tablespace/system/system01.dbf

shutdown된 DB를 mount 상태까지만 올린다.
SQL> startup mount

이동 경로를 Database에 알려 주자. 그리고 OPEN시킨다.

SQL> alter database rename
         file '/app/oradata/testdb/system01.dbf'
         to '/app/oradata/tablespace/system/system01.dbf';

SQL> alter database open;

SYSTEM tablespace가 이동 되었는지 확인 해 보자(원래 위치는 /app/oradata/testdb/)

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_data_files;

이번에는 tablespace를 삭제 시켜 보자.(tablespace의 datafile, table등 contents까지 모두 연쇄삭제)

SQL> drop tablespace MYTB
         including contents and datafiles cascade constraints;

2. temp(임시) tablespace 변경과 default temp tablespace 지정, temp tablespace 삭제

temp tablespace의 이름과 위치를 살펴 보자.

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_temp_files;

MYTMP라는 temp tablespace를 생성하고(datafile : mytmp01.dbf), 확인 해 보자.

SQL> create temporary tablespace MYTMP
         tempfile '/app/oradata/tablespace/mytmp01.dbf' size 10m;

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_temp_files;

MYTMP temp tablespace가 추가 되었고, temp tablespace는 2개이다. default temp tablespace가 어느것으로 되어 있는지 확인 해 보자.

SQL> select * from database_properties
         where property_name like '%TEMP%';

현재 TEMP라는 이름의 temp tablespace가 default로 되어 있다. 새로 만든 MYTMP로 default temp tablespace를 바꾸어 보자.
그리고 확인 한다.

SQL>alter database default temporary tablespace MYTMP;

SQL> select * from database_properties
         where property_name like '%TEMP%';

default temp tablespace가 변경 되었음을 알 수 있다. 마지막으로 temp tablespace를 삭제 해 보자.
그리고 확인한다.

SQL> drop tablespace TEMP;

SQL> select tablespace_name, bytes/1024/1024 mb, file_name
         from dba_temp_files;

SQL>select * from database_properties
        where property_name like '%TEMP%';

ORA-01144: File size (6553600 blocks) exceeds maximum of 4194303 blocks

ORA-01144: 파일크가 가 최대치 블록을 초과합니다

블록 사이즈 초과 오류시, 기존 테이블스페이스에 데이타 파일 추가

oracle tablespace name에 해당하는 용량이 다 차면 해당 tablespace name으로 하나 더 만들어준다.

ALTER TABLESPACE USERS add datafile '/data/ora12c/app/ora12c/oradata/BMTDB/users02.dbf' size 30000M;