ORA-01555: snapshot too old 원인과 해결 방법 완벽 정리 (Undo 튜닝 포함)

Oracle에서 ORA-01555 오류가 발생하면서 쿼리가 중단되셨나요?

“snapshot too old”라는 메시지 때문에 원인 파악이 어려우셨다면 이 글이 도움이 될 것입니다.

ORA-01555: snapshot too old 오류는 Oracle에서 매우 흔하게 발생하는 문제 중 하나로, 주로 Undo 데이터 소실, 긴 실행 시간의 쿼리, LOB 처리 오류 등과 관련이 있습니다. 특히 대용량 데이터를 다루는 환경, ETL 처리, 보고서 쿼리, 또는 LOB 컬럼 처리 시 자주 발생하죠.

이 글에서는 ORA-01555의 원인을 쉽고 명확하게 설명하고, 실무에서 즉시 적용할 수 있는 쿼리 기반 해결 방법까지 정리했습니다. Undo Retention 설정부터 LOB 컬럼 옵션 변경까지 한눈에 확인해보세요.

🔍 ORA-01555 오류 메시지

ORA-01555: snapshot too old: rollback segment number with name "" too small

📌 주요 원인

주요 원인	설명
Undo Retention 부족	쿼리 실행 중 Undo 데이터가 재사용됨
Undo Tablespace 공간 부족	오래된 Undo 블록이 강제로 삭제됨
대량 데이터 DML 동시 처리	다른 트랜잭션이 Undo를 빠르게 소모
LOB 컬럼 처리 시 오류	PCTVERSION 또는 RETENTION 미설정
느린 SELECT 쿼리	쿼리 시간이 너무 길어 Undo 유지 불가

✅ 해결 방법

1. Undo Retention 시간 증가

ALTER SYSTEM SET UNDO_RETENTION = 1800; -- 30분

2. Undo Tablespace 공간 늘리기

ALTER DATABASE DATAFILE '/path/undotbs01.dbf' RESIZE 2G;

3. Retention Guarantee 설정

Undo 공간이 부족해도 최소 보존 시간 확보

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

4. LOB 컬럼 오류 해결

LOB 데이터를 자주 수정할 경우 아래처럼 설정

ALTER TABLE your_table 
MODIFY lob_column 
(STORE AS SECUREFILE (RETENTION AUTO));

또는

ALTER TABLE your_table 
MODIFY lob_column 
(PCTVERSION 20);

5. 느린 SELECT 쿼리 튜닝

인덱스 사용 유도
배치 쿼리로 나누기 (ROWNUM, ROWNUM BETWEEN)
MERGE 또는 WITH절 사용으로 사전 정리

📈 Undo 사용량 모니터링 쿼리

SELECT 
    begin_time,
    end_time,
    undoblks,
    txncount,
    maxquerylen,
    ssolderrcnt
FROM 
    v$undostat
ORDER BY begin_time DESC;

📝 마무리 요약

항목	확인 포인트
Undo retention 설정	SHOW PARAMETER undo_retention
Undo tablespace 크기	DBA_DATA_FILES, V$UNDOSTAT
오류 쿼리 튜닝 여부	실행 계획 확인 (EXPLAIN PLAN)
LOB 저장 방식	SECUREFILE, PCTVERSION 옵션 적용 여부

ORA-01555 오류는 시스템의 Undo 관리, SQL 성능, LOB 처리 방식이 복합적으로 영향을 미치는 문제입니다. 위 방법들을 조합하면 대부분의 환경에서 해결 가능하며, 근본적으로는 Undo 공간 확보와 쿼리 시간 단축이 핵심입니다.

개인 블로그

이 블로그 검색