Oracle “Snapshot too old” 오류와 해결 방법
📌 오류 개요
Oracle에서 "Snapshot too old" 오류는 UNDO 영역 부족으로 인해 발생하는 오류입니다.
특히 긴 시간 실행되는 쿼리(대량 데이터 조회, 분석 쿼리 등)에서 많이 발생합니다.
(쿼리가 오래 수행되는 동안 UNDO영역에 기존 수행중이던 쿼리에 대한 데이터가 삭제됨)
1. “Snapshot too old” 오류 원인
오라클은 트랜잭션의 일관성을 유지하기 위해 UNDO 데이터(변경 이전 데이터를 보관하는 영역)를 사용합니다.
오류가 발생하는 주요 원인은 다음과 같습니다.
🔸 UNDO 영역 부족 → 오래된 UNDO 데이터가 삭제됨
🔸 긴 실행 시간의 쿼리 → 트랜잭션이 길어질수록 오래된 UNDO 데이터가 필요해짐
🔸 다른 트랜잭션에 의해 UNDO 공간이 덮어씌워짐
🔸 UNDO_RETENTION 설정 값이 낮음 → 오래된 데이터를 유지할 시간이 부족
💡 주로 발생하는 환경:
- 대량 데이터를 조회하는 OLAP 쿼리
- 긴 시간 실행되는 SELECT 쿼리
- 대량의 UPDATE/DELETE 작업
- 데이터웨어하우스에서 복잡한 분석 쿼리 실행 시
2. “Snapshot too old” 오류 해결 방법
주인장 팁 :
ORDER BY 구문을 추가하면 Snapshot too old오류가 날 정도의 큰 데이터들은 보통 Sort Area영역을 초과해서 Temp영역에 저장하게 되는데, 그러면 아무리 오랜시간 Fetch를 해도 Snapshot too old 오류는 발생하지 않습니다. 저는 배치 돌릴 때 DBA에게 UNDO Tablespace를 늘려달라 요청하기 불편해서 이 방법으로 해결한 적도 있답니다.😘 LOOP돌면서 대량데이터를 조회하는 경우 ORDER BY 를 추가 하면 큰 도움이 될거에요
🔹 해결책 1: UNDO Tablespace 크기 증가
UNDO 테이블스페이스의 크기를 늘리면 오래된 UNDO 데이터가 유지될 가능성이 높아집니다.
✅ 현재 UNDO 테이블스페이스 확인
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';✅ UNDO 테이블스페이스 크기 증가
ALTER DATABASE DATAFILE '/path_to_undo/undotbs01.dbf' RESIZE 4G;또는
ALTER DATABASE ADD DATAFILE '/path_to_undo/undotbs02.dbf' SIZE 2G AUTOEXTEND ON;🔹 해결책 2: UNDO_RETENTION 시간 증가
UNDO 데이터를 유지하는 시간을 늘리면 오류가 줄어듭니다.
✅ 현재 UNDO_RETENTION 설정 확인
SHOW PARAMETER UNDO_RETENTION;✅ UNDO_RETENTION 증가 (예: 900초 → 15분)
ALTER SYSTEM SET UNDO_RETENTION = 900;🔹 해결책 3: GUARANTEE 모드 설정
UNDO 데이터를 강제로 보존하도록 GUARANTEE 모드를 설정하면 트랜잭션 롤백을 위해 UNDO 데이터를 더 오래 유지할 수 있습니다.
✅ UNDO Tablespace의 보존 정책 확인
SELECT tablespace_name, retention
FROM dba_tablespaces
WHERE tablespace_name LIKE 'UNDO%';✅ UNDO Tablespace를 GUARANTEE 모드로 변경
ALTER DATABASE SET UNDO TABLESPACE undotbs1 RETENTION GUARANTEE;이렇게 하면 Oracle이 UNDO 공간을 강제로 보존하여 "Snapshot too old" 오류를 줄일 수 있습니다.
🔹 해결책 4: 대용량 쿼리를 더 작은 범위로 나누기
긴 실행 시간의 쿼리는 여러 개의 작은 쿼리로 나누는 것이 효과적입니다.
✅ 대량 DELETE 작업을 작은 배치로 실행
DELETE FROM large_table WHERE rownum <= 10000;
COMMIT;
DELETE FROM large_table WHERE rownum <= 10000;
COMMIT;
✅ SELECT 쿼리를 작은 범위로 나누기
SELECT * FROM large_table WHERE created_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD')
AND TO_DATE('2024-01-31', 'YYYY-MM-DD');🔹 해결책 5: TEMP Tablespace 증가 (TEMP 사용 시)
일부 "Snapshot too old" 오류는 TEMP 테이블스페이스가 부족하여 발생할 수도 있습니다.
✅ TEMP 테이블스페이스 확인
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_temp_files;✅ TEMP 테이블스페이스 크기 증가
ALTER DATABASE TEMPFILE '/path_to_temp/temp01.dbf' RESIZE 4G;✅ 새 TEMP 파일 추가
ALTER DATABASE ADD TEMPFILE '/path_to_temp/temp02.dbf' SIZE 2G AUTOEXTEND ON;3. 결론: 가장 효과적인 해결책
1️⃣ UNDO 테이블스페이스 크기 증가 → 가장 효과적
2️⃣ UNDO_RETENTION 값 증가 → 오래된 데이터 유지
3️⃣ GUARANTEE 모드 활성화 → UNDO 보존 보장
4️⃣ 쿼리를 작은 범위로 나누어 실행 → 긴 트랜잭션 방지
5️⃣ TEMP 테이블스페이스 크기 증가 → 일부 TEMP 사용 오류 방지
📌 추천:
- OLTP 환경에서는 UNDO 테이블스페이스 크기 증가 & UNDO_RETENTION 증가
- OLAP 환경에서는 쿼리를 분할 실행 & UNDO GUARANTEE 모드 활성화
🔹 “Snapshot too old” 오류는 주로 UNDO 부족 때문이므로, UNDO 관련 설정을 최적화하면 문제를 해결할 수 있습니다.