Oracle Temp Tablespace 증설 작업
오라클 데이터베이스에서 TEMP 테이블스페이스를 증설 작업은 대량의 정렬(Sort),
해시 조인(Hash Join) 또는 임시 테이블 작업 시 발생하는 공간 부족 문제를 해결하기 위해 필요.
---
1. 현재 TEMP 테이블스페이스 사용 현황 확인
1.1 TEMP 테이블스페이스 정보 조회
-- TEMP 테이블스페이스의 데이터 파일(tempfile) 정보 확인
SELECT
tablespace_name,
file_name,
bytes/1024/1024 AS "현재 크기(MB)",
maxbytes/1024/1024 AS "최대 크기(MB)",
autoextensible
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
1.2 실시간 사용량 확인
-- 현재 TEMP 공간 사용량 (동적 성능 뷰)
SELECT
*
FROM v$temp_space_header;
---
2. 증설 방법
2.1 기존 Tempfile 크기 확장 (Resize)
-- 1. Tempfile 경로 확인
SELECT
file_name
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
-- 2. 크기 확장 (예: 2GB로 변경)
ALTER DATABASE TEMPFILE '/경로/tempfile01.dbf' RESIZE 2048M;
2.2 새로운 Tempfile 추가 (권장)
ALTER TABLESPACE TEMP
ADD TEMPFILE '/경로/new_tempfile02.dbf'
SIZE 1024M
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED;
2.3 Autoextend 설정 활성화 (기존 Tempfile)
ALTER DATABASE TEMPFILE '/경로/tempfile01.dbf'
AUTOEXTEND ON
NEXT 50M
MAXSIZE 4096M; -- 최대 크기 지정
---
3. 작업 후 검증
3.1 변경 사항 확인
-- Tempfile 크기 및 설정 확인
SELECT
tablespace_name,
file_name,
bytes/1024/1024 AS "크기(MB)",
autoextensible
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
3.2 OS 레벨에서 Tempfile 확인
$ ls -lh /경로/tempfile01.dbf # 파일 크기 확인
---
4. 주의사항
1. 디스크 공간 모니터링:
- TEMP 테이블스페이스는 쿼리 수행 중 동적으로 확장되므로,
`MAXSIZE UNLIMITED` 설정 시 디스크 공간 고갈 위험이 있다.
2. 성능 영향:
- 대규모 Tempfile 추가/확장은 I/O 부하를 유발할 수 있다. 작업 시간을 비사용 시간대에 계획.
3. ASM 사용 시:
- ASM(Automatic Storage Management)을 사용하는 경우, Tempfile 경로는 `+DATA`와 같은 디스크 그룹 경로로 지정.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 2G;
4. 세션 영향:
- Tempfile 추가/확장은 실행 중인 세션에 영향을 주지 않는다.
---
5. 문제 해결
- ORA-03297: 파일 크기 초과:
- RESIZE 시 디스크 공간이 부족하면 발생. → 디스크 공간 확보 후 재시도.
- ORA-01144: 파일 크기 한계 초과:
- Tempfile 크기가 오라클 블록 크기 제한을 초과할 때 발생. → `MAXSIZE`를 조정하거나 새 Tempfile을 추가.
- ORA-01652: TEMP 공간 부족 오류
- 위 방법으로 Tempfile을 증설하거나, SQL 튜닝을 통해 임시 공간 사용량을 줄인다.
---
6. 자동화 스크립트 예시
-- 1. TEMP 테이블스페이스에 새 Tempfile 추가
ALTER TABLESPACE TEMP
ADD TEMPFILE '+DATA'
SIZE 2G
AUTOEXTEND ON
NEXT 500M
MAXSIZE 10G;
-- 2. 기존 Tempfile 확장
ALTER DATABASE TEMPFILE '+DATA/tempfile01.dbf' RESIZE 5G;
---
7. 추가 팁
- 임시 공간 사용 모니터링:
-- 현재 TEMP 공간을 사용 중인 세션 확인
SELECT
s.sid,
s.username,
u.tablespace,
u.blocks * (SELECT block_size FROM dba_tablespaces WHERE tablespace_name = 'TEMP')/1024/1024 AS "사용량(MB)"
FROM v$session s, v$tempseg_usage u
WHERE s.saddr = u.session_addr;
---
이 절차를 따라 TEMP 테이블스페이스를 안전하게 증설할 수 있다.
운영 환경에서는 반드시 사전 테스트를 수행하고, 디스크 공간 및 성능 영향을 고려.