다른 명령
템프테이블 스페이스
개요
- Temporary Tablespace 는 데이터베이스의 sort 작업과 글로벌 임시테이블을 저장하는데에 사용
- Sorting 을 필요로 하는 작업
- 인덱스 생성
- analyze
- select distinct
- order by
- group by
- union, intersect, minus
- Sort-Merge joins 등 ....
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 |
템프파일
- TempFile 이란 ?
- 일반 테이블스페이스에 데이터 파일을 할당 할때 오라클 프로세스는 데이터파일에 헤더를 기록하고 나머지공간을 포맷한다.
- 하지만 tempfile 로 생성된 데이터 파일의 경우 오라클은 헤더만 기록하고 나머지 공간은 그대로 두게 된다.
- 이것이 우리가 temp 데이터파일을 생성할때 일반 데이터파일 보다 속도가 굉장히 빠른이유
- 일반 데이터 파일과 다른점은 control file 에 기록되지 않는다는 것
- 그래서 Db를 재생성시에 tempfile을 다시 생성해주여야 함
TEMPFILE 삭제 와 생성
TEMPFILE 삭제
ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;
TEMPFILE 생성
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;
- Sorting 공간이 모자라서 System Tablespace 를 사용하는것을 방지하기 위해 적절하게 유저마다 Temp 영역을 할당
유저에게 기본 템프테이블스페이스 할당
- 유저를 생성 시 할당
CREATE USER SCOTT DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP;
- 유저 생성 후 할당
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
Temp Tablespace 생성
템프테이블 생성 방법
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;
기본 Temp Tablespace 지정
- "ALTER DATABASE" 명령을 사용하여 데이터베이스 생성시에 Default Temp Tablespace 지정
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
- Default Temp Tablespace 를 지정하지 않을 경우 SYSTEM TABLESPACE 가 Default 가 된다.
기본 Temp Tablespace 제약 사항
- Default Temporary Tablespace 는 반드시 TEMPORARY Type 이여야 한다.
- Default Temporary Tablespace 는 반드시 On-Line 상태여야 한다.
- Default Temporary Tablespace 는 다른 Temporary Tablespace를 생성하전에 Drop 할 수 없다.
기본 Temp Tablespace 조회
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
- Oracle 9i 이상
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#
Temp Tablespace 초기화 작업
- 시스템을 재부팅하여도 Temp Tablespace 영역이 초기화가 되지않아 수동으로 Temp Tablespace 초기화 작업 실시
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;
그룹 템프 테이블스페이스 사용법
temp tablespace group 특징 및 장점
- 1개 이상의 temp tablespace를 group으로 묶어서 관리하는 방식
- temp tablespace가 많이 요구되는 환경에서 1개 이상의 temp tablespace에 동시에 접근하여 병렬처리 작업을 수행
- 특정 operation이 temp공간을 모두 사용하는 것을 미연에 방지할 수 있다
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가 속해 있는 것 확인
TABLESPACE GROUP 제외
alter tablespace temp1 tablespace group '';
TEMPORARY TABLESPACE 모니터링
- V$TEMPSEG_USAGE를 통해 모니터링 가능
템프 테이블 스페이스 재생성
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;
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;