Day 36(Oracle)

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

오라클은 텍스트 기반의 데이터 베이스이다. MDB는 메모리 기반 데이터 베이스이고, 멀티미디어DB는 감각 기반 데이터 베이스로 사람이 느끼는 것으로 검색한다.

오라클이 대용량 DB라고 할수있는 것은 테이블 스페이스를 사용하기 때문이다. 테이블스페이스는 세그먼트를 저장하는 공간을 의미하는데 segment는 table과 index등이 있다. 테이블 스페이스는 1개 이상의 데이터베이스로 이루어져 있다.

세그먼트 스페이스가 아닌 테이블 스페이스라고 하는 이유는 테이블이 그만큼 중요하기 때문이다. 추가적으로 일반적인 시스템이라면 테이블의 크기가 인덱스의 크기보다 크다. 만약 인덱스가 테이블 보다 큰 상황이라면 DB가 작동했다 멈췄다 하는 증상이 발생할 수도 있다.

하드 드라이브에 직접 저장하면 데이터 저장에 한계가 하드 드라이브의 크기로 지정되지만, 테이블 스페이스를 사용하면 물리적 한계를 늘릴 수 있다.

테이블 스페이스는 SYSTEM 테이블 스페이스와 SYSTEM 테이블 스페이스가 아닌 3가지의 테이블 스페이스로 나눠진다.

SYSTEM테이블 스페이스는 오라클의 알파~오메가, 즉 모든것이라고 할정도로 중요하고, 데이터 딕셔너리 파일도 포함하고 있기 때문에 사용자가 사용(수정)해서도 안된다. 또한 생성 및 삭제도 불가능하며 오라클 설치시 자동생성된다. 그리고 SYSTEM테이블 스페이스의 빈 공간이 20%~10%인지 주기적으로 확인하고 만약 10%미만이라고 한다면 문제가 있는 것이다.

그 외의 테이블 스페이스는 일반사용자가 사용하는 테이블 스페이스인 usertablespace, 임시저장소용 테이블 스페이스인 temperarytablespace, 실행이 저장된 undotablespace가 있다. temperary tablespace는 실제 물리적으로 저장되지 않는다. 정렬요소(distinct, order by, group by, union, 인덱스가 아닌 컬럼의 조인하는 행위는 데이터를 저장하지 않고 temperary tablespace에 저장하고 값을 참조한다. 테이블 스페이스의 영역관리는 지역(local)적으로 관리되는 테이블 스페이스와 딕셔너리(dictionary)로 관리하는 테이블 스페이스가 있다. 딕셔너리로 관리하는 테이블 스페이스의 경우 저장공간을 할당받을때 딕셔너리가 지정하는 곳에 저장된다. 매번 저장공간을 할당받을때마다 딕셔너리에게 요청하므로 매번 트렌젝션과 병목현상이 발생한다. 자리가 부족한 경우에는 다시 요청해서 다시 할당받아야 한다.

지역적으로 관리하는 테이블 스페이스는 이러한 불편을 해소한 관리 방법이다. 데이터가 입력되면서 자신이 할당받은 위치를 체크하고 자동적으로 저장된다. 저장공간이 부족하게 되면 다른 공간도 선택하여 저장한다. 따라서 지역적으로 관리하는 테이블 스페이스는 딕셔너리로 관리하는 테이블 스페이스보다 비교적 단점이 적다.

usr T/S생성, usr01.dbf생성, 저장공간 할당받음

헤드block에 bitmap이라고 하는 정보를 저장한다. bitmap에는 저장된 블럭 정보가 들어가 있다. 이 방식으로 딕셔너리에게 공간할당받을 필요가 없어지는 것이다.

데이터가 저장될때 연속적인 block을 모아 묶은 block단위로 저장되는데 저장되는 block의 크기는 파라미터 중 db_block_size로 정해진다. 또한 번지수도 지정된다.

데이터 저장 형식을 extend 방식으로 지정하게 되면 공간을 extend로 정하고 이것을 bitmap에 체크한다. 연속적으로 받은 block을 extend(여기서는 단위다.)라고 한다. 따라서 첫번째 저장공간은 1 extend이다. 첫번째 block인 헤드 block은 테이블의 정보를 저장한다. 그리고 high water mark라는 만수점을 찍는데 찍은곳 안쪽까지만 데이터를 저장한다. 저장되는 데이터에는 순서가 없다. full table scan은 헤드블록 ~ H/M를 전부 읽는다. index table scan은 블록단위로 읽는다. 또한 오라클, DB2는 행단위로 락이 걸리고 그 외의 DB는 블록 단위로 락이 걸린다. undotablespace, temparytablespace 지역적으로 관리하는 테이블 스페이스로만 생성이 가능하다.

SELECT tablespace_name, status, contents, extent_management, segment_space_management
FROM dba_tablespace
ORDER BY 1; // 테이블 스페이스의 정보를 출력한다.
SELECT tablespace_name, bytes, file_name FROM dba_data_files
ORDER BY 1; // 데이터 파일의 정보를 출력한다.
SELECT tablespace_name, bytes, file_name FROM dba_temp_files; // 임시저장 파일의 정보를 출력한다.
SELECT ts#, file#, name FROM v$datafile; // 데이터 파라미터에 관한 정보를 출력한다
SELECT ts#, name FROM v$tablespace; // 임시저장파일 파라미터에 관한 정보를 출력한다.

SELECT t.name tablespace_name, d.bytes, d.name file_name
FROM v$tablespace t, v$datafile d
WHERE t.ts#=d.ts#
UNION ALL
SELECT t.name tablespace_name, p.bytes, p.name file_name
FROM v$tablespace t, v$tempfile p
WHERE t.ts#=p.ts#
ORDER BY 1; // 파라미터파일 설정 값을 참조하여 임시저장 테이블스페이스까지 출력한다.

CREATE TABLESPACE insa
DATAFILE '/app/ora12c/oradata/DB12/insa01.dbf' SIZE 1M; // 데이터 파일을 생성한다.

ALTER DATABASE DATAFILE
'/app/ora12c/oradata/DB12/insa01.dbf' RESIZE 2M; // 데이터파일의 크기를 변경한다.

ALTER TABLESPACE insa

ADD DATAFILE '/app/ora12c/oradata/DB12/insa02.dbf' SIZE 2M;
// 테이블 스페이스에 크기가 2M인 데이터 파일을 생성한다.

CREATE TABLESPACE usr

DATAFILE

'/app/ora12c/oradata/DB12/usr01.dbf' SIZE 2M AUTO EXTEND,

'/app/ora12c/oradata/DB12/usr02.dbf' SIZE 2M AUTO EXTEND ON NEXT 5M,

'/app/ora12c/oradata/DB12/usr03.dbf' SIZE 2M AUTO EXTEND ON NEXT 5M MAXSIZE 20M;
// USR 테이블 스페이스에 데이터 파일을 usr01의 크기는 2M인데 자동으로 용량이 확장되게 생성하고 usr02
// 파일은 크기가 2M인데 자동으로 5M씩 용량이 확장되게 생성하고, usr03파일은 크기가 2M인데 자동으로
// 5M씩 용량이 확장되는데 20M까지만 확장되게 생성한다. 하지만 이렇게 생성을하게 되면 usr01파일이 테 // 이블스페이스의 크기만큼 커지기 때문에 usr02, usr03파일을 사용하지 않는다.

SYSTEM테이블스페이스와 active중인 세그먼트는 삭제가 불가능하다. 공간이 할당된 테이블 스페이스도 삭제가 불가능 하다. 다른 tablespace에 저장된 부모 테이블이 삭제되지 않는다. DROP TABLESPACE 테이블이름 INCLUDING CONTENTS AND DATAFILES;는 테이블스페이스와 데이터 파일과 실제 데이터 파일을 삭제 한다.

SELECT * FROM database_properties WHERE property_name LIKE '%DEFAULT%';
// 기본 프로퍼티를 검색한다.

SELECT * FROM database_properties WHERE property_name LIKE '%DEFAULT_PERMAN%';

// 기본 퍼머넌트를 검색한다.

DROP TABLESPACE usr; // 테이블 스페이스를 삭제한다.

!rm /app/ora12c/oradata/DB12/usr01.dbf // 실제 파일을 삭제한다;