오라클 템프 테이블스페이스

1 템프테이블 스페이스[편집]

1.1 개요[편집]

Temporary Tablespace 는 데이터베이스의 sort 작업과 글로벌 임시테이블을 저장하는데에 사용
Sorting 을 필요로 하는 작업
1. 인덱스 생성
2. analyze
3. select distinct
4. order by
5. group by
6. union, intersect, minus
7. Sort-Merge joins 등 ....

1.2 Temp Tablespace 종류[편집]

종류	공간관리 방식	Sort Segment Reuse	Extent Reuse	Datafile	생성문법
Temporary	Locally Managed	세그먼트 사용후에도 드롭되지 않고 재사용	항상 재사용	tempfiles	CREATE TEMPORARY TABLESPACE
Type Temporary	Dictionary Managed	세스먼트 사용후에도 드롭되지 않으나 DB 리스타트시 재생성됨	DB 리스타트 이전까지 드롭되지 않고 재사용됨	datafiles	CREATE TABLESPACE ... TYPE TEMPORARY
Permanent	Dictionary Managed	재사용하지 않음	재사용 하지 않음	datafiles	CREATE TABLESPACE

1.3 템프파일[편집]

TempFile 이란 ?

일반 테이블스페이스에 데이터 파일을 할당 할때 오라클 프로세스는 데이터파일에 헤더를 기록하고 나머지공간을 포맷한다.
하지만 tempfile 로 생성된 데이터 파일의 경우 오라클은 헤더만 기록하고 나머지 공간은 그대로 두게 된다.
1. 이것이 우리가 temp 데이터파일을 생성할때 일반 데이터파일 보다 속도가 굉장히 빠른이유
일반 데이터 파일과 다른점은 control file 에 기록되지 않는다는 것
1. 그래서 Db를 재생성시에 tempfile을 다시 생성해주여야 함

1.3.1 TEMPFILE 삭제 와 생성[편집]

1.3.1.1 TEMPFILE 삭제[편집]

ALTER DATABASE 
      TEMPFILE '/oradata/temp02.dbf' 
      DROP INCLUDING DATAFILES;

1.3.1.2 TEMPFILE 생성[편집]

ALTER TABLESPACE TEMP 
      ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

Sorting 공간이 모자라서 System Tablespace 를 사용하는것을 방지하기 위해 적절하게 유저마다 Temp 영역을 할당

1.4 유저에게 기본 템프테이블스페이스 할당[편집]

유저를 생성 시 할당

CREATE USER SCOTT 
       DEFAULT TABLESPACE DATA 
       TEMPORARY TABLESPACE TEMP;

유저 생성 후 할당

ALTER USER SCOTT
      TEMPORARY TABLESPACE TEMP;

1.5 Temp Tablespace 생성[편집]

1.5.1 템프테이블 생성 방법[편집]

1) Permanant Type 방식 (~7i)

Permanant Type 의 경우 DB Controlfile에 기록되므로 복제DB 생성이나 복구 등에 있어서 Temp Datafile이 필요

CREATE TABLESPACE TEMP
       DATAFILE '/oradata/mytemp_01.tmp'
       SIZE 100M;

2) Temporary Type 방식 (~ 8.0)

CREATE TABLESPACE TEMP
       DATAFILE  '/oradata/temp_01.tmp'
       SIZE 100M TEMPORARY;

3) TEMPFILE 방식 (8i~)

CREATE TEMPORARY TABLESPACE TEMP
       TEMPFILE '/oradata/mytemp_01.tmp' SIZE 200M
       EXTENT MANAGEMENT LOCAL 
       UNIFORM SIZE 10M;

최상의 퍼포먼스를 내기 위해서는 UNIFORM SIZE 를 init parameter 에 설정된 sort_area_size 의 배수로 지정하는 것이 좋다.

4) OMF(Oracle Managed Filesystem) 방식 (9i ~)

CREATE TEMPORARY TABLESPACE TEMP;

1.6 기본 Temp Tablespace 지정[편집]

"ALTER DATABASE" 명령을 사용하여 데이터베이스 생성시에 Default Temp Tablespace 지정

ALTER DATABASE 
      DEFAULT TEMPORARY TABLESPACE TEMP;

Default Temp Tablespace 를 지정하지 않을 경우 SYSTEM TABLESPACE 가 Default 가 된다.

1.6.1 기본 Temp Tablespace 제약 사항[편집]

Default Temporary Tablespace 는 반드시 TEMPORARY Type 이여야 한다.
Default Temporary Tablespace 는 반드시 On-Line 상태여야 한다.
Default Temporary Tablespace 는 다른 Temporary Tablespace를 생성하전에 Drop 할 수 없다.

1.6.2 기본 Temp Tablespace 조회[편집]

SELECT * FROM DATABASE_PROPERTIES 
 where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Oracle 9i 이상

1.6.3 Temp Tablespace Usage(사용율) 모니터링[편집]

SELECT B.NAME 
     , B.BYTES/1024/1024 || ' M' TOTAL    
     , A.BYTES_USED/1024/1024 || ' M' USED
     , (B.BYTES-A.BYTES_USED)/1024/1024 || ' M' "FREE"
     , ROUND(A.BYTES_USED/B.BYTES * 100) || '%' "USAGE"
 FROM (SELECT BYTES_USED,FILE_ID 
         FROM V$TEMP_EXTENT_POOL) A
     , (SELECT BYTES,FILE#,NAME 
         FROM V$TEMPFILE) B
 WHERE A.FILE_ID = B.FILE#

1.7 Temp Tablespace 초기화 작업[편집]

시스템을 재부팅하여도 Temp Tablespace 영역이 초기화가 되지않아 수동으로 Temp Tablespace 초기화 작업 실시

1.7.1 Temp Tablespace 초기화 작업 순서[편집]

1) 임시 Temporary tablespace 생성

CREATE TEMPORARY TABLESPACE TEMP_IMSI
       TEMPFILE '/oradata/mytemp_01.tmp' SIZE 200M
       EXTENT MANAGEMENT LOCAL 
       UNIFORM SIZE 10M;
-- omr일경우는 create temporary tablespace TEMP_IMSI;

2) default Temporary tablespace 변경

alter database 
      default temporary tablespace TEMP_IMSI; -- 임시로 생성한 템프테이블 스페이스

3) 기존 Temp tablespace 삭제

drop tablespace TEMP 
     including contents and datafiles;

4) Temp tablespace 생성(기본 30G로 생성 최대 60G까지 확장)

create temporary tablespace TEMP 
       tempfile 'D:\ORACLE\TEMP01.DBF' 
       size 10000M 
       reuse autoextend on next 100m 
       maxsize 20000m,
       'D:\ORACLE\TEMP02.DBF' 
       size 10000M 
       reuse autoextend on next 100m
       maxsize 20000m,
       'D:\ORACLE\TEMP03.DBF' 
       size 10000M 
       reuse autoextend on next 100m 
       maxsize 20000m;

5) default Temporary tablespace 원상복귀

alter database 
      default temporary tablespace TEMP;

임시생성한 Temp tablespace 삭제

drop tablespace TEMP_IMSI
     including contents and datafiles;

1.8 그룹 템프 테이블스페이스 사용법[편집]

1.8.1 temp tablespace group 특징 및 장점[편집]

1개 이상의 temp tablespace를 group으로 묶어서 관리하는 방식
temp tablespace가 많이 요구되는 환경에서 1개 이상의 temp tablespace에 동시에 접근하여 병렬처리 작업을 수행
특정 operation이 temp공간을 모두 사용하는 것을 미연에 방지할 수 있다

1.8.2 TABLESPACE GROUP 설정[편집]

현재 temp2 tablespace를 default temp tablespace로 사용하고 있고 temp2에는 temp01.dbf, temp02.dbf 2개의 tempfile이 포함되어 있음
새로운 temporary tablespace인 temp1을 생성하고 tablespace group을 지정
기존에 default로 사용하던 temp2 tablespace에 group을 지정
default temporary tablespace를 group1로 변경
group1에는 temp1과 temp2가 속해 있는 것 확인

1.8.3 TABLESPACE GROUP 제외[편집]

alter tablespace temp1 tablespace group '';

1.8.4 TEMPORARY TABLESPACE 모니터링[편집]

V$TEMPSEG_USAGE를 통해 모니터링 가능

1.9 템프 테이블 스페이스 재생성[편집]

1) 대체될 Temp tablespace를 생성

CREATE TEMPORARY TABLESPACE "TEMP_1" 
       TEMPFILE '/ora_data/temp01_1.dbf' SIZE 1024M AUTOEXTEND OFF,
                '/ora_data/temp02_1.dbf' SIZE 1024M AUTOEXTEND OFF,
                '/ora_data/temp03_1.dbf' SIZE 4096M AUTOEXTEND OFF
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2) 신규 생성한 테이블을 기본 Temp tablespace로 지정

ALTER DATABASE 
      DEFAULT TEMPORARY TABLESPACE TEMP_1

3)기존 사용한 Temp tablespace는 삭제

DROP TABLESPACE "TEMP" 
     INCLUDING CONTENTS AND DATAFILES;

1.10 UNDO tablespace 재생성[편집]

1) 생성

CREATE UNDO TABLESPACE "UNDOTBS1_1" 
       DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ASITSNT\UNDOTBS01_1.DBF' 
       SIZE 20480M 
       AUTOEXTEND OFF
       EXTENT MANAGEMENT 
       LOCAL AUTOALLOCATE;

2) 변경

ALTER SYSTEM 
  SET UNDO_TABLESPACE = UNDOTBS1_1;

3) 삭제

DROP TABLESPACE UNDOTBS1 
     INCLUDING CONTENTS AND DATAFILES;