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

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

Oracle 데이터베이스를 운영하다 보면 다양한 오류를 마주하게 됩니다. 그중에서도 ORA-01555: Snapshot Too Old 오류 / ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 %s 수에 "%s" 이름으로 된 것이 너무 작습니다. 오류는 비교적 흔하게 발생하는 문제로, 특히 대용량 트랜잭션을 수행하는 환경에서 자주 나타납니다. 이번 포스팅에서는 이 오류의 원인과 해결 방법을 알아보겠습니다.

---

1. ORA-01555 오류란?

ORA-01555: Snapshot Too Old 오류는 트랜잭션이 롤백 세그먼트(Undo Tablespace)에서 필요한 데이터를 찾지 못할 때 발생합니다. Oracle에서는 데이터 일관성을 유지하기 위해 변경된 데이터를 Undo 세그먼트에 저장하고 이를 통해 읽기 일관성을 제공합니다. 하지만 Undo 데이터가 오래되거나 덮어씌워지면 필요한 데이터를 찾지 못해 Snapshot Too Old 오류가 발생합니다.

---

2. 주요 원인

이 오류가 발생하는 주요 원인은 다음과 같습니다:

1. Undo Tablespace 크기 부족
   • 트랜잭션이 실행되는 동안 필요한 Undo 데이터가 오래 유지되지 못하고 덮어씌워지는 경우 발생합니다.
2. 장시간 실행되는 쿼리
   • 대용량 데이터를 조회하는 SELECT 문이 실행되는 동안 Undo 데이터가 삭제되면 오류가 발생할 수 있습니다.
3. UNDO_RETENTION 설정값 부족
   • Oracle에서 Undo 데이터를 유지하는 시간(UNDO_RETENTION)이 너무 짧게 설정되면, 필요한 데이터가 삭제될 수 있습니다.
4. LOB 데이터 처리 문제
   • LOB(Large Object) 데이터를 관리하는 방식에 따라 Undo 공간을 많이 소모할 수 있습니다.

---

3. 해결 방법

(1) Undo Tablespace 크기 증가

Undo 테이블스페이스의 크기를 확인하고 부족하면 증가시킵니다.

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1';

크기가 부족하면 파일을 확장합니다:

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

또는 새로운 Undo 테이블스페이스를 추가할 수도 있습니다:

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/oradata/undotbs02.dbf' SIZE 2G;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

(2) UNDO_RETENTION 값 조정

Undo 데이터를 더 오래 유지하도록 설정합니다.

ALTER SYSTEM SET UNDO_RETENTION = 900;

또한, RETENTION GUARANTEE 옵션을 사용하면 Undo 데이터가 보존되도록 강제할 수 있습니다.

ALTER DATABASE DATAFILE '/u01/oradata/undotbs01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

(3) LOB 데이터 관리 방식 변경

LOB 데이터를 다룰 때 Undo 공간 사용을 최소화하려면 SECUREFILE을 사용하고, RETENTION 설정을 조정합니다.

ALTER TABLE my_table MODIFY lob(my_column) (STORE AS SECUREFILE RETENTION AUTO);

또는, NOCACHE 옵션을 설정하여 Undo 사용량을 줄일 수도 있습니다.

ALTER TABLE my_table MODIFY lob(my_column) (STORE AS BASICFILE NOCACHE);

(4) 장시간 실행되는 쿼리 최적화

• 인덱스를 활용하여 풀 테이블 스캔을 줄입니다.
• PARALLEL 옵션을 사용하여 실행 시간을 단축합니다.
• 데이터를 여러 배치(batch)로 나누어 실행합니다.

SELECT /*+ PARALLEL(my_table, 4) */ * FROM my_table;

---

 

ORA-01555: Snapshot Too Old 오류는 Undo 공간 부족, 장시간 실행되는 트랜잭션, 잘못된 LOB 설정 등으로 인해 발생합니다. 이를 방지하려면 Undo 테이블스페이스를 충분히 확보하고, UNDO_RETENTION 값을 조정하며, 장시간 실행되는 쿼리를 최적화하는 것이 중요합니다.