[ORACLE] ORA-01652 오류 해결 가이드 : TEMP Tablespace 공간 부족 문제 완전 정복

[ORACLE] ORA-01652

 

ORA-01652: unable to extend temp segment by N in tablespace TEMP

Oracle DB에서 발생하는 ORA-01652 오류는 TEMP 테이블스페이스에 더 이상 임시 세그먼트를 확장할 공간이 없을 때 발생합니다. 이 오류는 대규모 정렬, 해시 조인, 집계 쿼리, 인덱스 생성 등에서 자주 등장하며, DB 성능 이슈로 직결되는 중요한 문제입니다. 본 글에서는 ORA-01652 오류의 근본 원인부터 진단, 실무 적용 가능한 해결 전략까지 전문가 관점에서 실전 가이드로 제공하며, 운영 환경에서 안정성을 확보하기 위한 권장 설정도 함께 제시합니다.

---

1. ORA-01652 오류란?

오류 메시지:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

의미: 임시 테이블스페이스(TEMP)에 할당된 공간이 부족하여 SQL 연산 도중 필요한 작업 영역을 확보하지 못한 상황입니다.

주요 발생 조건:

• ORDER BY, GROUP BY, DISTINCT 쿼리
• 인덱스 생성이나 병렬 처리 쿼리
• 해시 조인 또는 대용량 서브쿼리

---

2. 오류 원인 비교표

원인	설명	해결 방법
TEMP 공간 부족	임시 세그먼트 생성 공간 부족	TEMP 파일 추가 또는 자동 확장
쿼리 비효율	불필요한 정렬/조인으로 TEMP 낭비	인덱스 추가, 쿼리 리팩토링
병렬 처리 과도	병렬 세션 증가로 TEMP 고갈	병렬도 제한 또는 힌트 사용
다중 사용자 경쟁	여러 세션이 동시에 TEMP 사용	쿼리 분산 또는 스케줄 조정

---

3. 실무 사례 분석

사례: 온라인 쇼핑몰의 통계 배치 작업 중, 매일 새벽 동일 시간에 ORA-01652 오류 다수 발생

원인 분석:

• 대용량 주문 데이터를 정렬 후 집계
• 사용자 정의 집계함수로 인덱스를 활용하지 못함
• 쿼리 병렬 처리(PARALLEL hint) 사용으로 TEMP 세션 증가

해결:

1. TEMP 테이블스페이스에 10GB 데이터파일 추가
2. 통계 쿼리 리팩토링 (서브쿼리 → WITH절 + 인덱스 활용)
3. 병렬도 제한 설정 (PARALLEL_DEGREE_POLICY 수동 전환)

---

4. TEMP 사용량 확인 방법

-- TEMP 테이블스페이스 사용량 확인
SELECT tablespace_name, SUM(blocks)*8/1024 AS MB_USED
FROM v$tempseg_usage
GROUP BY tablespace_name;

-- TEMP 데이터파일 확인
SELECT file_name, bytes/1024/1024 AS MB
FROM dba_temp_files;

---

5. TEMP 공간 확보를 위한 조치

① TEMP 테이블스페이스 확장

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/temp02.dbf' SIZE 5G;

② TEMP 파일 자동 확장 설정

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/temp01.dbf'
AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

③ TEMP 세그먼트 수동 정리 (비권장)

일반적으로 TEMP 세그먼트는 세션 종료 후 자동 정리되며, 수동 정리는 추천하지 않습니다. 하지만 세션이 비정상적으로 남는 경우 다음 SQL로 확인할 수 있습니다:

SELECT s.sid, s.serial#, u.tablespace, u.segfile#, u.segblk#, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr = u.session_addr;

---

6. 쿼리 튜닝으로 TEMP 사용 최소화

다음은 TEMP 사용을 줄이기 위한 일반적인 쿼리 튜닝 팁입니다:

• 불필요한 ORDER BY 제거
• JOIN 방식 변경 (HASH JOIN → NESTED LOOP)
• 분할 조회(PAGINATION) 도입
• 인덱스 생성 또는 HINT 활용

---

7. DBA 권장 설정 및 예방 전략

전략	설명	명령어 예시
AUTOEXTEND 설정	TEMP 자동 확장	ALTER DATABASE TEMPFILE ... AUTOEXTEND ON;
모니터링 스크립트	TEMP 사용량 주기적 점검	CRON + SQL*Plus 활용
쿼리 리팩토링	집계/정렬 최소화	WITH절, 인덱스 설계
병렬도 제한	PARALLEL 자동 설정 방지	ALTER SYSTEM SET parallel_degree_policy=manual;

---

8. 결론

ORA-01652 오류는 단순히 TEMP 공간을 늘리는 것으로 해결되기도 하지만, 반복적으로 발생한다면 쿼리 구조 개선과 시스템 리소스 최적화가 병행되어야 합니다. 이 오류는 단기 대응보다는 장기적 성능 개선 관점에서 접근해야 하며, 정기적인 모니터링과 쿼리 검토를 통해 사전 예방하는 것이 중요합니다.

---

출처

• Oracle® Database Administrator's Guide 19c
• Oracle Support Doc ID 1012388.6 - ORA-1652 Troubleshooting Guide