ORA-01555 오류 해결 방법 | Snapshot too old 예외 완전 정복

ORA-01555: Snapshot Too Old 오류 해결법

대량의 데이터를 반복 처리하거나 커서(Cursor)를 사용한 PL/SQL 작업 중 다음과 같은 오류 메시지를 만난 적 있으신가요?

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

이 오류는 트랜잭션 중 참조한 데이터의 UNDO 정보가 사라져버렸을 때 발생합니다. 즉, Oracle이 이전 상태의 데이터를 복원하려고 했지만 UNDO 영역에서 해당 정보를 찾을 수 없을 때 생기는 오류입니다.

📚 목차

• 1. ORA-01555 오류란?
• 2. 주요 발생 원인
• 3. 해결 방법
• 4. 예제 및 실습
• 5. 예방 팁 및 권장사항

1. ORA-01555 오류란?

ORA-01555 오류는 Oracle이 과거 시점의 데이터를 읽으려고 할 때, 필요한 UNDO 정보가 이미 삭제되어 더 이상 접근할 수 없을 때 발생합니다.

일반적으로 길게 실행되는 쿼리 또는 커서 기반 루프에서 자주 발생합니다.

2. 주요 발생 원인

• ⏱️ 트랜잭션이 너무 오래 실행됨
• 📦 UNDO 테이블스페이스 설정이 부족하거나 보존 기간이 짧음
• 🔁 FOR LOOP나 커서로 대량 데이터 반복 처리
• 🛠️ LOB 컬럼 처리 중 임시 테이블 공간 부족

3. 해결 방법

✔️ 1. UNDO 보존 시간 증가

DBA 권한이 있다면 UNDO 관련 설정을 조정할 수 있습니다.

ALTER SYSTEM SET UNDO_RETENTION = 900;

UNDO_RETENTION을 높이면 오래된 트랜잭션도 복원 가능성이 높아집니다.

✔️ 2. CURSOR 대신 BULK 처리

-- 나쁜 예 (FOR LOOP)
FOR rec IN (SELECT * FROM big_table) LOOP
  -- 처리 로직
END LOOP;

-- 좋은 예 (BULK COLLECT + FORALL)
DECLARE
  TYPE t_tab IS TABLE OF big_table%ROWTYPE;
  l_data t_tab;
BEGIN
  SELECT * BULK COLLECT INTO l_data FROM big_table;

  FORALL i IN l_data.FIRST..l_data.LAST
    UPDATE big_table SET col = 'Y' WHERE id = l_data(i).id;
END;

✔️ 3. 쿼리 시간 단축

• 필요한 인덱스가 존재하는지 확인
• WHERE 절 최적화로 처리 범위 최소화

✔️ 4. LOB 컬럼은 CACHE 옵션 변경

ALTER TABLE my_table MODIFY lob_column (CACHE);

4. 예제 및 실습

예제 1: 오래된 커서 루프 사용

DECLARE
  CURSOR cur IS SELECT * FROM big_table;
BEGIN
  FOR rec IN cur LOOP
    DBMS_LOCK.SLEEP(1); -- 일부러 지연
    NULL; -- 처리 로직
  END LOOP;
END;

👉 데이터가 많고 지연이 크면 ORA-01555 발생 가능

5. 예방 팁 및 권장사항

• ✅ UNDO_RETENTION 값 충분히 설정
• ✅ FOR LOOP 대신 BULK COLLECT와 FORALL 사용
• ✅ 쿼리 성능을 높여 트랜잭션 시간 단축
• ✅ LOB 처리 시 NOCACHE 대신 CACHE 고려
• ✅ 자주 쓰는 테이블에는 적절한 인덱스를 적용

---

🔚 마무리

ORA-01555 오류는 성능 이슈로 이어질 수 있는 중요한 경고입니다. 특히 대량 데이터를 다룰 때 구조와 로직을 미리 최적화하는 것이 중요합니다.

실무에서 자주 발생하는 오류이니 위 내용을 잘 숙지해두시면 큰 도움이 될 거예요!

다음 포스트에서는 트리거 관련 오류 (ORA-04098)를 다룰 예정입니다. 기대해주세요 😊