ORACLE ORA-01652 오류: "임시 세그먼트를 확장할 수 없습니다" 해결 방법

1. ORA-01652 오류 개요

ORA-01652: unable to extend temp segment by X in tablespace TEMP
이 오류는 Oracle 데이터베이스에서 임시 세그먼트(TEMP Segment)를 확장할 수 없을 때 발생한다. 주로 대용량 정렬(ORDER BY, GROUP BY), 해시 조인(HASH JOIN), DISTINCT 연산 등을 수행할 때 TEMP 테이블스페이스의 공간이 부족하면 나타난다.

---

2. ORA-01652 오류 원인

1) TEMP 테이블스페이스 크기 부족

쿼리 실행 시 필요한 TEMP 공간보다 TEMP 테이블스페이스의 가용 공간이 적은 경우 발생.
해결 방법: TEMP 테이블스페이스 크기를 늘리거나 자동 확장을 활성화해야 한다.

2) 자동 확장(AUTOEXTEND) 설정이 비활성화됨

TEMP 파일의 AUTOEXTEND 옵션이 OFF로 설정되어 있으면, 공간이 부족할 때 자동 확장이 되지 않아 오류 발생.
해결 방법: AUTOEXTEND를 ON으로 변경하여 TEMP 공간을 동적으로 확장할 수 있도록 설정해야 한다.

3) 특정 세션이 TEMP 공간을 과도하게 사용

대량 데이터 조회 시 특정 세션이 TEMP 공간을 독점적으로 사용하여 다른 세션이 TEMP를 확보하지 못할 수 있다.
해결 방법: TEMP 사용량이 높은 세션을 확인하고, 불필요한 세션을 종료해야 한다.

4) SQL 실행 시 TEMP 사용량이 과도함 (SQL 튜닝 필요)

해시 조인, 정렬, 그룹화 연산 등으로 인해 TEMP 공간이 과도하게 사용될 수 있다.
해결 방법: 실행 계획(EXPLAIN PLAN)을 확인하고, 인덱스를 활용하거나 조인 방식을 최적화하여 TEMP 사용량을 줄여야 한다.

---

3. ORA-01652 해결 방법

(1) TEMP 테이블스페이스 크기 확인

먼저, TEMP 테이블스페이스의 현재 크기와 자동 확장 여부를 확인해야 한다.

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB, AUTOEXTENSIBLE FROM DBA_TEMP_FILES;

• SIZE_MB: TEMP 테이블스페이스의 현재 크기(MB).
• AUTOEXTENSIBLE: YES인 경우 자동 확장 가능, NO인 경우 자동 확장 불가능.

---

(2) TEMP 테이블스페이스 크기 확장

TEMP 테이블스페이스 크기가 부족하면 다음 명령어를 실행하여 공간을 확장할 수 있다.

① 기존 TEMP 파일 크기 확장

ALTER DATABASE TEMPFILE '/path_to_temp/temp01.dbf' RESIZE 10G;

TEMP 파일 크기를 10GB로 늘린다.

② TEMP 파일 자동 확장 설정

ALTER DATABASE TEMPFILE '/path_to_temp/temp01.dbf' AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

• NEXT 500M: 필요할 때 500MB씩 증가.
• MAXSIZE 20G: 최대 20GB까지 확장 가능.

③ 새로운 TEMP 파일 추가

ALTER DATABASE ADD TEMPFILE '/path_to_temp/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

TEMP 테이블스페이스에 새로운 TEMP 파일을 추가하여 부하를 분산할 수 있다.

---

(3) TEMP 사용량이 높은 세션 확인 및 종료

특정 세션이 TEMP를 과도하게 사용하는 경우, TEMP 사용량이 높은 세션을 확인하고 필요 시 종료해야 한다.

① 현재 TEMP 사용량 조회

SELECT s.username, s.sid, t.blocks * t.tablespace_size / 1024 / 1024 AS TEMP_USED_MB FROM v$session s JOIN v$tempseg_usage t ON s.saddr = t.session_addr ORDER BY TEMP_USED_MB DESC;

해당 쿼리를 실행하면 TEMP를 많이 사용 중인 세션을 확인할 수 있다.

② 특정 세션 종료

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

불필요한 세션이 TEMP를 과도하게 사용 중이면 해당 세션을 강제 종료하여 TEMP 공간을 확보할 수 있다.

---

4. SQL 튜닝을 통한 TEMP 사용량 최소화

TEMP 테이블스페이스를 확장하는 것도 중요하지만, SQL 튜닝을 통해 TEMP 사용량을 줄이는 것이 근본적인 해결책이다.

(1) HASH JOIN 대신 NESTED LOOP 사용

HASH JOIN은 TEMP를 많이 사용하므로, NESTED LOOP로 변경하면 TEMP 사용량을 줄일 수 있다.

SELECT /*+ USE_NL(B C) */ A.ID, B.DATA, C.DATA FROM A JOIN B ON A.ID = B.A_ID JOIN C ON B.ID = C.B_ID WHERE A.ID = :param;

• USE_NL(B C): NESTED LOOP 조인을 강제하여 TEMP 사용 최소화.

---

(2) ORDER BY, GROUP BY 대상 컬럼에 인덱스 추가

ORDER BY나 GROUP BY 연산 시 TEMP 공간을 과도하게 사용할 수 있으므로, 해당 컬럼에 인덱스를 추가하면 성능을 개선할 수 있다.

CREATE INDEX IDX_SORT ON A(ID);

이 인덱스를 활용하면 정렬 연산을 줄일 수 있다.

---

(3) DISTINCT 대신 EXISTS 또는 JOIN 사용

DISTINCT는 정렬 연산이 필요하므로 TEMP 사용량이 많다. EXISTS를 활용하면 인덱스만 사용하여 TEMP 사용을 줄일 수 있다.

SELECT A.* FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.ID = B.A_ID);

---

5. 결론

ORA-01652 오류는 주로 TEMP 테이블스페이스 부족으로 인해 발생하며, 다음과 같은 방법으로 해결할 수 있다.

 

해결 방법	설명
TEMP 테이블스페이스 크기 조정	TEMP 파일 크기 확장 및 자동 확장 설정
TEMP 사용량 높은 세션 종료	v$tempseg_usage 조회 후 불필요한 세션 종료
SQL 튜닝 (NESTED LOOP, INDEX, EXISTS 활용)	TEMP 사용을 최소화하는 방식으로 쿼리 최적화

TEMP 공간을 확장하는 것만으로는 근본적인 해결이 어려울 수 있으며, SQL 튜닝을 병행하여 불필요한 TEMP 사용을 줄이는 것이 중요하다.