[오라클 에러] ORA-01555 snapshot too old - 5가지 원인과 해결방법 (UNDO 일관성 읽기 모델 완벽 정리)
테스트 환경: Oracle 11g / 12c / 19c / 21c
월말 결산 시즌, 새벽 4시. 4시간 동안 돌던 결산 리포트가 마지막 단계에서 ORA-01555: snapshot too old 로 실패합니다. 처음부터 다시 돌리면 또 4시간. 그동안 운영팀과 회계팀에서 전화가 빗발칩니다.
ORA-01555는 DBA가 가장 자주 마주치면서도 가장 자주 오해하는 에러입니다. "UNDO 늘리면 된다" 는 단순한 답은 절반만 맞습니다. 어떨 때는 UNDO를 10배 늘려도 재발하고, 어떨 때는 SQL 한 줄만 바꿔도 해결됩니다.
이번 글에서는 ORA-01555의 본질인 읽기 일관성(Read Consistency) 모델부터 시작해서, 5가지 원인을 진단하고 근본 해결하는 방법까지 정리했습니다. 특히 Fetch Across Commit, LOB 세그먼트 케이스, expdp FLASHBACK_TIME같이 한국어 자료에서 잘 다루지 않는 영역까지 포함했습니다.
장애 대응 중이라면 긴급 응급 처치부터 보세요.
에러 메시지 전문
ORA-01555: snapshot too old: rollback segment number XX with name "_SYSSMU..." too small
ORA-01555: 스냅샷이 너무 오래되었습니다: %s 롤백 세그먼트 번호가 너무 작습니다
19c 이상에서는 다음과 같이 더 상세한 메시지가 나오기도 합니다.
ORA-01555: snapshot too old: rollback segment number XX with name "_SYSSMU..." too small
ORA-22924: snapshot too old (LOB 케이스)
ORA-22924는 LOB 관련 ORA-01555입니다. 일반 ORA-01555와 원인이 완전히 다르므로 구분해야 합니다.
★ ORA-01555의 본질 - 읽기 일관성(Read Consistency) 모델
다른 블로그에서 잘 다루지 않는 부분이지만, 이걸 이해해야 ORA-01555를 진짜로 해결할 수 있습니다.
일관성 읽기란
오라클은 트랜잭션이 시작된 시점의 데이터를 일관되게 보여주는 보장을 제공합니다. 쿼리가 4시간 걸려도 그 4시간 동안 다른 사용자가 데이터를 바꿔도, 쿼리는 시작 시점의 데이터를 봅니다.
어떻게 가능한가
[Time 10:00] 리포트 쿼리 시작
↓ "이 시점의 데이터로 끝까지 본다"
[Time 10:30] OLTP가 row X를 update → COMMIT
↓ 오라클은 row X의 변경 전 값을 UNDO에 보관
[Time 12:00] 리포트 쿼리가 row X에 도달
↓ "10:00 시점 값"이 필요함
↓ UNDO에서 변경 전 값을 가져옴
↓ ✅ 일관된 결과 반환
ORA-01555가 발생하는 순간
[Time 10:00] 리포트 쿼리 시작
[Time 10:30] OLTP가 row X update → COMMIT
[Time 11:00] UNDO_RETENTION (15분) 만료 → UNDO 공간 재사용
[Time 12:00] 리포트가 row X에 도달
↓ "10:00 시점 값"이 필요한데
↓ UNDO에 그 값이 없음 (덮어쓰여짐)
↓ ❌ ORA-01555 발생
한 줄 요약: ORA-01555는 "필요한 과거 데이터의 UNDO가 사라져서 일관성을 보장할 수 없음" 의 에러입니다.
UNDO 세그먼트의 3가지 상태
UNDO 영역의 각 블록은 다음 중 하나의 상태를 가집니다.
상태 의미 재사용 가능?
| ACTIVE | 현재 진행 중인 트랜잭션이 사용 중 | ❌ 절대 불가 |
| UNEXPIRED | 커밋됐지만 일관성 읽기를 위해 보관 (UNDO_RETENTION 기간 내) | △ 공간 부족 시 재사용 |
| EXPIRED | UNDO_RETENTION 지남 | ✅ 자유롭게 재사용 |
핵심: ACTIVE는 절대 못 건드리지만, UNEXPIRED는 공간이 부족하면 재사용될 수 있습니다. 이 경우 ORA-01555가 발생합니다.
상태 확인 쿼리
SELECT tablespace_name, status,
ROUND(SUM(bytes)/1024/1024, 2) AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;
출력 예시:
TABLESPACE_NAME STATUS MB
--------------- --------- --------
UNDOTBS1 ACTIVE 120
UNDOTBS1 UNEXPIRED 3500
UNDOTBS1 EXPIRED 50 ← 이게 너무 작으면 위험
EXPIRED 공간이 거의 없으면 오라클이 UNEXPIRED를 침범하기 시작합니다. 이게 ORA-01555의 직접적 신호입니다.
긴급 응급 처치
운영 중에 ORA-01555가 자주 발생하고 있다면 우선 다음을 시도하세요.
즉시 조치 (5분 안에)
1) UNDO_RETENTION 늘리기
-- 현재 값 확인
SHOW PARAMETER undo_retention
-- 1시간(3600초)으로 증가
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;
주의: 이는 "목표값"일 뿐입니다. UNDO 테이블스페이스 공간이 부족하면 오라클은 이 값을 무시합니다.
2) UNDO 테이블스페이스 확장
-- 자동 확장 활성화
ALTER DATABASE DATAFILE '/u01/oradata/db1/undotbs01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 32G;
-- 또는 직접 추가
ALTER DATABASE DATAFILE '/u01/oradata/db1/undotbs01.dbf'
RESIZE 10G;
3) RETENTION GUARANTEE 활성화 (신중히)
-- UNDO 테이블스페이스 이름 확인
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';
-- 보장 모드 활성화
ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
경고: RETENTION GUARANTEE는 UNDO 데이터를 절대 덮어쓰지 않도록 보장하지만, 공간 부족 시 새 트랜잭션이 ORA-30036으로 실패할 수 있습니다. 일관성 vs 가용성의 트레이드오프를 이해하고 사용하세요.
이걸로 응급은 처리되지만, 근본 원인은 따로 진단해야 합니다.
빠른 진단 체크리스트
-- 1) 최근 ORA-01555 발생 횟수
SELECT SUM(ssolderrcnt) AS ora_1555_count
FROM v$undostat;
-- 2) 최장 쿼리 시간 (지난 7일)
SELECT MAX(maxquerylen) AS max_query_seconds
FROM v$undostat;
-- 3) 현재 UNDO 사용 압력
SELECT MAX(unxpstealcnt) AS undo_pressure_indicator
FROM v$undostat;
-- 4) UNDO 세그먼트별 상태
SELECT status, COUNT(*), ROUND(SUM(bytes)/1024/1024, 2) AS mb
FROM dba_undo_extents
GROUP BY status;
maxquerylen이 현재 undo_retention보다 크면 사고 발생률이 높습니다. unxpstealcnt가 0이 아니면 이미 UNEXPIRED 침범이 시작된 상태입니다.
원인 1: UNDO_RETENTION이 최장 쿼리보다 짧음 (가장 흔함)
ORA-01555의 60% 이상이 이 케이스입니다.
진단
-- 현재 설정
SHOW PARAMETER undo_retention -- 예: 900 (15분)
-- 실제 최장 쿼리 시간
SELECT MAX(maxquerylen) FROM v$undostat; -- 예: 14400 (4시간)
설정 15분 vs 실제 최장 4시간 → 사고 100% 발생.
해결
UNDO_RETENTION을 최장 쿼리보다 충분히 길게 설정:
-- 4시간(14400초) + 여유 1시간 = 5시간으로 설정
ALTER SYSTEM SET undo_retention = 18000 SCOPE=BOTH;
실무 권장값:
- OLTP 중심: 3600 ~ 7200초 (1~2시간)
- 배치/리포트 환경: 21600초 이상 (6시간)
- DW: 43200초 이상 (12시간)
TUNED_UNDORETENTION 확인 (★ 11g+)
11g부터 오라클이 자동으로 UNDO 보존 기간을 조정합니다.
SELECT MIN(tuned_undoretention) AS effective_min,
MAX(tuned_undoretention) AS effective_max
FROM v$undostat;
tuned_undoretention이 설정한 undo_retention보다 작다면 오라클이 공간 부족 때문에 자동 단축한 것입니다. 이 경우 UNDO 테이블스페이스 자체를 늘려야 합니다.
원인 2: UNDO 테이블스페이스 자체가 작음
UNDO_RETENTION은 충분한데 UNDO 공간이 부족한 케이스입니다.
진단
-- UNDO 테이블스페이스 크기와 자동 확장 여부
SELECT tablespace_name, file_name, autoextensible,
ROUND(bytes/1024/1024, 2) AS size_mb,
ROUND(maxbytes/1024/1024, 2) AS max_mb
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';
-- 사용량
SELECT tablespace_name,
ROUND(SUM(CASE WHEN status = 'EXPIRED' THEN bytes ELSE 0 END)/1024/1024, 2) AS expired_mb,
ROUND(SUM(CASE WHEN status = 'UNEXPIRED' THEN bytes ELSE 0 END)/1024/1024, 2) AS unexpired_mb,
ROUND(SUM(CASE WHEN status = 'ACTIVE' THEN bytes ELSE 0 END)/1024/1024, 2) AS active_mb
FROM dba_undo_extents
GROUP BY tablespace_name;
UNDO 사이즈 산정 공식
필요한 UNDO 크기 = 최장 쿼리 시간 × 초당 UNDO 생성량 × 안전계수(1.5~2)
-- 초당 UNDO 생성량
SELECT MAX(undoblks / ((end_time - begin_time) * 24 * 3600)) AS undo_blocks_per_sec,
ROUND(MAX(undoblks / ((end_time - begin_time) * 24 * 3600)) * 8192 / 1024 / 1024, 2) AS undo_mb_per_sec
FROM v$undostat;
예시: 초당 UNDO 1MB × 최장 쿼리 4시간(14400초) × 1.5 = 21.6GB
이보다 작으면 UNDO 부족이 발생할 수 있습니다.
해결
-- 데이터파일 추가
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE
'/u01/oradata/db1/undotbs02.dbf' SIZE 10G AUTOEXTEND ON;
-- 또는 기존 파일 자동 확장
ALTER DATABASE DATAFILE '/u01/oradata/db1/undotbs01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
★ 원인 3: Fetch Across Commit (코드 사이드 문제)
이 케이스는 한국어 자료에 깊이 다루지 않는 영역인데, 개발자 코드의 근본 문제입니다. UNDO를 아무리 늘려도 이 패턴이 있으면 ORA-01555가 재발합니다.
문제 패턴
-- ❌ Fetch Across Commit 안티 패턴
DECLARE
CURSOR c_data IS SELECT * FROM big_table;
BEGIN
FOR rec IN c_data LOOP
UPDATE big_table SET col = new_value WHERE id = rec.id;
COMMIT; -- ← 같은 테이블의 update를 매 row commit
END LOOP;
END;
왜 ORA-01555가 발생하나
- 커서는 처음 OPEN될 때 SCN(System Change Number)을 기록
- 같은 테이블에 UPDATE + COMMIT 반복 → UNDO 폭증
- UNDO_RETENTION 지나면서 UNDO 재사용
- 커서가 후반 row에 도달했을 때 처음 SCN 시점의 데이터 재구성 불가 → ORA-01555
해결 - BULK COLLECT 패턴
-- ✅ 권장 패턴
DECLARE
CURSOR c_data IS SELECT id, col FROM big_table;
TYPE t_id IS TABLE OF big_table.id%TYPE;
TYPE t_col IS TABLE OF big_table.col%TYPE;
l_id t_id;
l_col t_col;
BEGIN
OPEN c_data;
LOOP
FETCH c_data BULK COLLECT INTO l_id, l_col LIMIT 1000;
EXIT WHEN l_id.COUNT = 0;
FORALL i IN 1..l_id.COUNT
UPDATE big_table SET col = new_value WHERE id = l_id(i);
COMMIT; -- 1000개 단위 commit
END LOOP;
CLOSE c_data;
END;
또는 커서를 다시 열어서 처리하는 방식:
-- 청크 단위로 분리 처리
BEGIN
LOOP
UPDATE big_table SET col = new_value
WHERE rownum <= 1000 AND processed = 'N';
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
END;
식별 방법
운영 중 발생한 ORA-01555의 어떤 SQL이 원인인지 찾기:
-- 장기 실행 SQL 확인
SELECT sql_id, executions,
ROUND(elapsed_time/1000000, 2) AS elapsed_sec,
SUBSTR(sql_text, 1, 100) AS sql_preview
FROM v$sqlarea
WHERE elapsed_time/1000000 > 900 -- 15분 이상
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
★ 원인 4: LOB 세그먼트의 ORA-01555 (특수 케이스)
LOB(CLOB/BLOB) 컬럼이 있는 테이블에서 발생하는 ORA-01555는 UNDO 테이블스페이스를 사용하지 않습니다. 일반 UNDO 설정을 아무리 늘려도 해결 안 됩니다.
무엇이 다른가
LOB는 자체적으로 UNDO를 관리합니다.
모드 파라미터 의미
| PCTVERSION | 0~100 | LOB 세그먼트의 N%를 undo로 사용 (기본 10%) |
| RETENTION | (시간) | UNDO_RETENTION 값을 사용 |
둘 중 하나만 사용합니다.
진단
-- LOB 컬럼의 retention 설정 확인
SELECT owner, table_name, column_name, segment_name,
pctversion, retention, cache, logging
FROM dba_lobs
WHERE owner = '소유자명'
AND table_name = '테이블명';
PCTVERSION이 10이면 LOB 세그먼트의 10%만 undo로 사용 — DML이 많으면 부족합니다.
해결
PCTVERSION 증가:
ALTER TABLE my_table MODIFY LOB(lob_column) (PCTVERSION 30);
RETENTION으로 변경:
ALTER TABLE my_table MODIFY LOB(lob_column) (RETENTION);
RETENTION 모드는 UNDO_RETENTION 파라미터를 그대로 사용해서 일관성을 제공합니다. 대용량 LOB DML 환경에서는 RETENTION 모드를 권장합니다.
★ 원인 5: expdp의 FLASHBACK_TIME / FLASHBACK_SCN
데이터펌프 export 중에 ORA-01555가 발생하는 케이스입니다.
무엇이 문제인가
expdp는 일관된 export를 위해 시작 시점의 SCN을 사용합니다. 큰 테이블을 export하는 동안 다른 트랜잭션이 많이 발생하면 UNDO 부족으로 ORA-01555가 발생합니다.
# expdp가 자동으로 사용하는 옵션
expdp user/pwd schemas=APP_USER \
flashback_time=systimestamp \
directory=DATA_PUMP_DIR \
dumpfile=app.dmp
해결
방법 1: 시간 분산 export
스키마를 나눠서 작은 단위로 export.
expdp user/pwd tables=APP_USER.big_table_1 \
flashback_time=systimestamp ...
expdp user/pwd tables=APP_USER.big_table_2 \
flashback_time=systimestamp ...
방법 2: 한가한 시간대로 이동
DML이 적은 시간(새벽 2~4시)에 실행. UNDO 압력이 적어 ORA-01555 발생 가능성 감소.
방법 3: UNDO 일시적 증가
export 직전 UNDO 늘리고 끝나면 원복.
-- export 전
ALTER SYSTEM SET undo_retention = 14400 SCOPE=BOTH;
-- export 후
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;
운영 환경 권장 표준
장기 안정 운영을 위한 권장 설정입니다.
UNDO 설정 표준
-- 1) 자동 UNDO 관리 활성화 (11g+ 기본)
ALTER SYSTEM SET undo_management = 'AUTO' SCOPE=SPFILE;
-- 2) UNDO_RETENTION을 최장 쿼리보다 충분히 길게
ALTER SYSTEM SET undo_retention = 7200 SCOPE=BOTH; -- 2시간
-- 3) UNDO 테이블스페이스 자동 확장
ALTER DATABASE DATAFILE '/path/undotbs01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
모니터링 쿼리 (일일 점검)
-- 1) ORA-01555 발생 추세 (7일)
SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24') AS hour,
MAX(ssolderrcnt) AS ora_1555_count,
MAX(maxquerylen) AS max_query_sec
FROM v$undostat
WHERE begin_time > SYSDATE - 7
GROUP BY TO_CHAR(begin_time, 'YYYY-MM-DD HH24')
HAVING MAX(ssolderrcnt) > 0
ORDER BY hour DESC;
-- 2) UNDO 압력 지표
SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24') AS hour,
unxpstealcnt AS unexpired_steal,
expstealcnt AS expired_steal,
expblkreucnt AS expired_reuse
FROM v$undostat
WHERE unxpstealcnt > 0
OR expstealcnt > 0;
권장 알림 임계값
지표 경고 위험
| ssolderrcnt (시간당 ORA-01555 수) | >= 1 | >= 5 |
| UNEXPIRED steal 발생 | 알림 | 즉시 조치 |
| UNDO 사용률 | 70% | 90% |
| TUNED_UNDORETENTION < UNDO_RETENTION | 알림 | RETENTION GUARANTEE 검토 |
그래도 안 풀린다면
위 5가지로도 해결되지 않는 드문 케이스:
- Oracle 버그: 일부 패치 버전에서 ORA-01555 false alarm 발생 (My Oracle Support Doc 269814.1)
- RAC 환경의 GES 락: 노드 간 동기화 지연으로 발생
- Active Data Guard에서 standby query: max_data_delay 설정 영향
- Flashback Database 활성화: Flashback log와 UNDO 상호 작용
마무리
ORA-01555는 "UNDO 부족"이라는 표면적 메시지와 달리, 실제로는 5가지 다른 원인이 있으며 각각 다르게 해결해야 합니다. 무작정 UNDO를 늘리기 전에 다음을 점검하세요.
- v$undostat으로 실제 최장 쿼리 시간 측정 — UNDO_RETENTION이 충분한가
- UNDO 테이블스페이스 사이즈 산정 공식 적용 — 단순 추측 금지
- Fetch Across Commit 패턴 코드 리뷰 — UNDO 늘려도 해결 안 되는 케이스
- LOB 컬럼이 있다면 PCTVERSION/RETENTION 별도 점검
- expdp 시 UNDO 일시 증가 또는 시간 분산
가장 효과적인 예방은 개발 단계에서 BULK COLLECT 패턴을 표준화하는 것입니다. PL/SQL 코드 리뷰 항목에 "Fetch Across Commit 없는가"를 포함시키면 운영에 들어간 후 발생하는 ORA-01555의 상당 부분을 막을 수 있습니다.
비슷한 케이스를 겪으셨거나, 위 방법으로도 해결되지 않은 상황이 있다면 댓글로 공유해 주세요.