오라클 템프 테이블스페이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 템프테이블 스페이스[편집]
1.1 개요[편집]
- Temporary Tablespace 는 데이터베이스의 sort 작업과 글로벌 임시테이블을 저장하는데에 사용
- Sorting 을 필요로 하는 작업
- 인덱스 생성
- analyze
- select distinct
- order by
- group by
- union, intersect, minus
- 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 로 생성된 데이터 파일의 경우 오라클은 헤더만 기록하고 나머지 공간은 그대로 두게 된다.
- 이것이 우리가 temp 데이터파일을 생성할때 일반 데이터파일 보다 속도가 굉장히 빠른이유
- 일반 데이터 파일과 다른점은 control file 에 기록되지 않는다는 것
- 그래서 Db를 재생성시에 tempfile을 다시 생성해주여야 함
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#
2 = Temp Tablespace 초기화 작업[편집]
- 시스템을 재부팅하여도 Temp Tablespace 영역이 초기화가 되지않아 수동으로 Temp Tablespace 초기화 작업 실시
2.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;
2.2 그룹 템프 테이블스페이스 사용법[편집]
2.2.1 temp tablespace group 특징 및 장점[편집]
- 1개 이상의 temp tablespace를 group으로 묶어서 관리하는 방식
- temp tablespace가 많이 요구되는 환경에서 1개 이상의 temp tablespace에 동시에 접근하여 병렬처리 작업을 수행
- 특정 operation이 temp공간을 모두 사용하는 것을 미연에 방지할 수 있다
2.2.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가 속해 있는 것 확인
2.2.3 TABLESPACE GROUP 제외[편집]
alter tablespace temp1 tablespace group '';
2.3 템프 테이블 스페이스 재생성[편집]
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;
2.4 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;