ORA-01652 unable to extend temp segment — 임시 테이블스페이스 부족 에러 원인·해결·예방 완전 정리
대형 리포트 쿼리, 인덱스 재생성, 대량 정렬·해시 조인을 실행하다가 갑자기 ORA-01652: unable to extend temp segment by N in tablespace TEMP를 받는 경우가 있다. Oracle이 PGA 메모리에서 처리할 수 없는 대용량 정렬·해시 작업을 디스크로 spill하다가 임시 테이블스페이스에 새 익스텐트를 할당하지 못해 실패한 상태다. DBA에게 자주 들어오는 장애 보고 중 하나다.
본 글은 ORA-01652의 정확한 의미, 5가지 발생 원인(TEMP 공간 부족·동시 정렬 폭주·대용량 SORT·PGA 부족·세그먼트 누수), V$SORT_USAGE·V$TEMPFILE 모니터링, Tempfile 확장·PGA_AGGREGATE_TARGET 조정·쿼리 튜닝을 활용한 해결 방법, 인접 에러(ORA-01650·01653·30036·04030)와의 차이까지 정리한다.
이 글의 구성
01 에러 메시지와 TEMP 동작 원리
| 항목 | 내용 |
|---|---|
| 에러 코드 | ORA-01652 |
| 영문 | unable to extend temp segment by N in tablespace TEMP |
| 한국어 | 테이블스페이스 TEMP에서 N(으)로 임시 세그먼트를 확장할 수 없습니다 |
| 의미 | 정렬·해시조인·GTT·인덱스 생성 작업이 TEMP에 새 익스텐트 할당 실패 |
| 발생 영역 | 대량 SORT·HASH·DISTINCT·UNION·인덱스 리빌드·GTT 작업 |
TEMP 테이블스페이스의 역할
PGA 메모리 안에서 처리 가능한 정렬·해시 작업은 디스크를 사용하지 않는다. 하지만 PGA 한계를 초과하는 대용량 작업은 디스크로 spill되어 TEMP 테이블스페이스에 임시 세그먼트를 만든다. 작업이 끝나면 자동 반환된다. 동시 작업량이 많거나 TEMP 크기가 작으면 새 익스텐트 할당이 실패해 ORA-01652가 발생한다.
💡 핵심 관찰 — PGA가 작을수록 TEMP 사용이 늘어난다
ORA-01652 해결은 두 가지 축으로 좁혀진다 ━ TEMP 공간 자체를 늘리는 방향과 TEMP 사용량을 줄이는 방향이다. PGA_AGGREGATE_TARGET 증가, 쿼리 튜닝(인덱스 활용으로 SORT 제거), 동시성 제어가 후자에 해당한다.
02 발생 원인 5가지
원인 1 — TEMP 테이블스페이스 절대 크기 부족
DBA가 충분한 TEMP를 할당하지 않았거나 AUTOEXTEND를 꺼 둔 환경. 대량 작업 시 즉시 한계에 도달한다. Tempfile 추가 또는 RESIZE로 확장한다.
원인 2 — 동시 대량 정렬·해시조인 폭주
야간 배치와 OLTP 트랜잭션이 같은 시간대에 충돌해 TEMP를 동시에 점유하는 경우. 어느 한 작업이 끝나기 전에 다른 작업이 익스텐트를 요청해 실패한다. 동시성 스케줄 조정이 근본책이다.
원인 3 — 대용량 ORDER BY / GROUP BY / DISTINCT
인덱스 없는 컬럼으로 수천만 행을 정렬하면 거의 100% TEMP를 쓴다. UNION(DISTINCT)도 내부적으로 정렬을 수반한다. 인덱스 추가, UNION ALL 대체, 파티셔닝으로 SORT 부담을 줄인다.
원인 4 — PGA_AGGREGATE_TARGET 부족
PGA가 작으면 메모리 정렬이 디스크로 쉽게 spill된다. PGA를 충분히 키우면 TEMP 의존도가 자연스럽게 줄어든다. 실제 PGA 사용량은 V$PGASTAT으로 모니터링한다.
원인 5 — Orphan 임시 세그먼트 누수
세션이 비정상 종료되면 TEMP 세그먼트가 회수되지 않고 남아 공간을 점유한다. V$SORT_USAGE에 활성 세션이 없는데도 TEMP 사용량이 크면 누수다. 새 TEMP 생성 후 옛 TEMP 드롭으로 정리한다.
03 진단·모니터링 SQL
-- TEMP 테이블스페이스 파일 현황
SELECT tablespace_name, file_name,
bytes/1024/1024 AS mb,
autoextensible, maxbytes/1024/1024 AS max_mb
FROM dba_temp_files;
-- TEMP 가용 공간
SELECT tablespace_name,
tablespace_size/1024/1024 AS total_mb,
free_space/1024/1024 AS free_mb
FROM dba_temp_free_space;
-- 현재 TEMP 사용 중인 세션·SQL
SELECT s.sid, s.serial#, s.username, s.sql_id,
u.tablespace, u.segtype,
u.blocks * TO_NUMBER(p.value) / 1024 / 1024 AS used_mb
FROM v$sort_usage u
JOIN v$session s ON u.session_addr = s.saddr
CROSS JOIN v$parameter p
WHERE p.name = 'db_block_size'
ORDER BY used_mb DESC;
-- PGA 사용 통계
SELECT name, value/1024/1024 AS mb
FROM v$pgastat
WHERE name IN ('aggregate PGA target parameter',
'total PGA inuse',
'over allocation count');
04 해결 방법과 쿼리 튜닝
TEMP 확장 (즉각 대응)
-- 기존 Tempfile 크기 확대
ALTER DATABASE TEMPFILE '/u01/temp01.dbf' RESIZE 16G;
-- 새 Tempfile 추가 + AUTOEXTEND
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/temp02.dbf'
SIZE 4G
AUTOEXTEND ON NEXT 256M
MAXSIZE 32G;
-- PGA 키우기 (자동 메모리 관리 OFF인 경우)
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G SCOPE=BOTH;
Orphan 세그먼트 정리 (장기 대응)
활성 세션이 없는데도 TEMP 사용량이 줄지 않으면 누수다. 다음 흐름으로 정리한다.
-- 1. 새 TEMP 테이블스페이스 생성
CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/u01/temp_new.dbf' SIZE 8G AUTOEXTEND ON;
-- 2. 기본 TEMP를 새 것으로 변경
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
-- 3. 기존 TEMP 사용 중 세션이 없는지 확인 후 드롭
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
쿼리 튜닝 — SORT 회피
| 패턴 | 회피 방법 |
|---|---|
| 인덱스 없는 ORDER BY | 정렬 컬럼에 인덱스 추가, 인덱스 스캔으로 SORT 제거 |
| 큰 DISTINCT | EXISTS 서브쿼리, GROUP BY로 대체 |
| UNION | 중복 없음 보장 시 UNION ALL로 변경 |
| 인덱스 리빌드 | ONLINE + PARALLEL + 분할 처리 |
ORA-01652 해결은 두 가지 — TEMP를 키우거나, TEMP를 덜 쓰게 만들거나. 단기 대응은 Tempfile 추가, 장기 대응은 PGA 증가와 쿼리 튜닝이다.
━ PGA가 커지면 디스크 spill이 줄어든다
05 인접 에러와의 차이
| 에러 코드 | 대상 영역 | 전형적 시나리오 |
|---|---|---|
| ORA-01652 | TEMP 테이블스페이스 | 정렬·해시·GTT·인덱스 생성 |
| ORA-01650 | 롤백 세그먼트 (구버전) | 수동 UNDO 환경의 롤백 영역 부족 |
| ORA-01653 | 일반 테이블 영역 | 테이블 데이터 익스텐트 확장 실패 |
| ORA-30036 | UNDO 테이블스페이스 | 대량 DML 시 UNDO 부족 |
| ORA-04030 | OS·PGA 메모리 | PGA 자체 부족, OS 메모리 한계 |
핵심 구분 ━ ORA-01652는 TEMP 디스크 영역의 정렬·해시 spill 영역 부족, ORA-04030은 PGA 메모리 자체 부족, ORA-01653은 일반 데이터 테이블스페이스, ORA-30036은 UNDO 영역의 같은 문제다. 메시지의 "tablespace" 이름으로 즉시 구분된다.
06 자주 묻는 질문 5가지
Q1TEMP와 UNDO 테이블스페이스는 어떻게 다른가
TEMP는 정렬·해시·GTT 같은 휘발성 작업 영역이고, UNDO는 트랜잭션 복원·읽기 일관성을 위한 변경 전 이미지 저장 영역이다. TEMP는 작업이 끝나면 자동 반환되고 백업 대상도 아니다. UNDO는 트랜잭션 commit·rollback·읽기 일관성 보장에 필수다. 두 영역은 완전히 다른 목적이며 부족 시 발생하는 에러도 다르다(ORA-01652 vs ORA-30036).
Q2PGA를 늘리면 TEMP 사용이 정말 줄어드나
그렇다. Oracle은 PGA 안에서 정렬·해시 작업을 처리하다 PGA 한계를 넘으면 TEMP로 spill한다. PGA가 작으면 작은 정렬도 spill되고, PGA가 충분하면 메모리 안에서 완료된다. V$PGASTAT의 over allocation count·extra bytes read/written 지표로 PGA 부족 여부를 확인할 수 있다. 일반 OLTP 시스템은 PGA 2～8GB 정도가 흔하며 대형 배치 시스템은 16GB+를 사용한다.
Q3GTT(Global Temporary Table)도 TEMP를 사용하나
GTT 데이터는 세션 임시 영역으로 TEMP 테이블스페이스에 저장된다. 12c 이전에는 GTT의 undo도 일반 UNDO 테이블스페이스를 썼지만, 12c+ Temporary UNDO(TEMP_UNDO_ENABLED=TRUE)를 활성화하면 GTT undo도 TEMP에서 처리된다. 이렇게 하면 일반 UNDO 압박이 줄고 GTT 동시 사용량이 늘어난다.
Q4누수된 임시 세그먼트는 어떻게 정리하나
V$SORT_USAGE에 활성 세션이 없는데도 DBA_TEMP_FREE_SPACE 사용량이 크면 누수다. 안전한 방법은 새 TEMP 테이블스페이스를 만들어 DEFAULT를 그것으로 변경한 뒤, 기존 TEMP를 INCLUDING CONTENTS AND DATAFILES로 드롭하는 것이다. 운영 중인 DB에서 실행 전에는 영향 받을 세션이 없는지 반드시 확인한다.
Q5정렬 자체를 줄이는 쿼리 튜닝 팁은
첫째, ORDER BY 컬럼에 인덱스를 만들어 인덱스 스캔으로 SORT 제거. 둘째, UNION 대신 UNION ALL 사용(중복 없음이 보장될 때). 셋째, DISTINCT 대신 EXISTS·GROUP BY로 변환. 넷째, ROWNUM이나 FETCH FIRST n ROWS로 결과 수 제한. 다섯째, 대량 인덱스 리빌드는 PARALLEL + ONLINE 옵션. 실행 계획(EXPLAIN PLAN)에서 SORT 연산자를 추적해 의도와 다른 정렬을 발견한다.
07 결론
ORA-01652는 PGA 한계를 넘는 정렬·해시 작업이 TEMP 테이블스페이스에 spill되다가 익스텐트 할당에 실패한 상태다. 단기 대응은 Tempfile 확장, 장기 대응은 PGA 증가와 쿼리 튜닝이다.
실무 원칙은 다음과 같다.
첫째, V$SORT_USAGE·DBA_TEMP_FREE_SPACE·V$PGASTAT을 모니터링 표준 항목에 포함한다. 사후 추적이 아닌 사전 감지가 핵심이다.
둘째, TEMP 테이블스페이스에 AUTOEXTEND ON과 충분한 MAXSIZE를 둔다. 정적 크기는 항상 부족하다.
셋째, PGA_AGGREGATE_TARGET을 작업 부하에 맞게 설정한다. PGA가 커지면 TEMP 의존도가 자연스럽게 줄어든다.
넷째, 대용량 ORDER BY·DISTINCT·UNION은 인덱스·UNION ALL·EXISTS로 SORT를 회피하는 방향으로 튜닝한다.
다섯째, 야간 배치와 OLTP 동시성 충돌을 스케줄로 분리하고, 누수된 TEMP 세그먼트는 새 테이블스페이스 교체 방식으로 정리한다.
✅ ORA-01652 트러블슈팅 체크리스트
본 글은 Oracle Database 에러 ORA-01652의 일반적 원인과 해결 방법을 정리한 자료다. 운영 환경 적용 전 테스트 환경에서 충분히 검증한다.
#ORA01652 #unableToExtendTempSegment #오라클에러 #TEMP테이블스페이스 #PGA_AGGREGATE_TARGET #VSORTUSAGE #DBATEMPFREESPACE #Tempfile #정렬튜닝 #UNIONALL #ORA01650 #ORA01653 #ORA30036 #ORA04030 #DBA튜닝
댓글