Oracle ORA-01555 오류 원인과 해결 방법 완벽 가이드

ORA-01555 snapshot too old: rollback segment too small 는?

ORA-01555 에러는 Oracle 데이터베이스에서 쿼리가 실행되는 동안 해당 쿼리가 참조해야 할 과거 시점의 데이터(Undo 데이터)가 이미 덮어쓰여 더 이상 존재하지 않을 때 발생합니다. Oracle은 읽기 일관성(Read Consistency)을 보장하기 위해 쿼리 시작 시점의 SCN(System Change Number)을 기준으로 데이터를 읽는데, 해당 SCN 이전의 Undo 정보가 재사용되거나 소멸되면 이 에러가 발생합니다. 주로 대용량 배치 작업, 장시간 실행되는 쿼리, 또는 Undo Tablespace가 너무 작게 설정된 환경에서 빈번하게 나타납니다.

---

주요 발생 원인

1. Undo Tablespace 크기 부족 또는 Undo Retention 설정 미흡

Oracle이 이전 버전의 데이터를 Undo Segment에 보관하는 시간(UNDO_RETENTION)이 너무 짧거나, Undo Tablespace의 물리적 크기가 부족할 경우 쿼리 도중 필요한 Undo 데이터가 덮어써질 수 있습니다. 특히 OLTP와 대용량 배치 쿼리가 혼재하는 환경에서는 Undo 경합이 심화되어 이 에러가 자주 발생합니다.

2. 장시간 실행되는 쿼리 (Long-Running Query)

쿼리 실행 시간이 길수록, 쿼리 시작 시점의 SCN에 해당하는 Undo 데이터가 재사용될 가능성이 높아집니다. 예를 들어 수천만 건의 데이터를 Full Table Scan으로 처리하는 야간 배치 쿼리나 리포팅 쿼리가 대표적인 사례입니다. 이 경우 쿼리 자체가 오래 걸리는 동안 다른 DML 트랜잭션이 Undo를 지속적으로 재사용하므로 충돌이 발생합니다.

3. 잦은 커밋(Fetch Across Commit) 패턴

애플리케이션 코드에서 커서(Cursor)를 열어 데이터를 Fetch하는 도중 중간에 COMMIT을 수행하는 패턴, 즉 “Fetch Across Commit”이 발생할 경우 ORA-01555가 유발될 수 있습니다. 커밋 이후 Undo 정보가 해제되면 이전에 열려 있던 커서가 참조해야 할 Undo 블록이 사라지기 때문입니다. 이는 잘못된 애플리케이션 설계에서 비롯되는 경우가 많으며, 특히 PL/SQL 루프 내부에서 자주 발생합니다.

---

해결 방법

1. Undo Retention 값 및 Undo Tablespace 크기 증가

현재 Undo 설정을 확인하고, UNDO_RETENTION 파라미터와 Tablespace 크기를 늘립니다.

-- 현재 Undo 설정 확인
SHOW PARAMETER UNDO;

-- UNDO_RETENTION 값 변경 (단위: 초, 3600 = 1시간)
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE = BOTH;

-- Undo Tablespace 크기 확인
SELECT tablespace_name, SUM(bytes)/1024/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
GROUP BY tablespace_name;

-- Undo Tablespace 데이터파일 추가
ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/u01/oradata/orcl/undotbs02.dbf'
SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

-- Undo Tablespace Autoextend 확인 및 설정
ALTER DATABASE DATAFILE '/u01/oradata/orcl/undotbs01.dbf'
AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

2. RETENTION GUARANTEE 옵션 활성화

Undo 데이터가 UNDO_RETENTION 시간 동안 강제로 보존되도록 설정합니다. 이 옵션을 사용하면 Undo 공간이 부족하더라도 오래된 Undo를 덮어쓰지 않아 ORA-01555를 예방할 수 있습니다.

-- RETENTION GUARANTEE 활성화
ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

-- 설정 확인
SELECT tablespace_name, retention
FROM dba_tablespaces
WHERE tablespace_name = 'UNDOTBS1';

3. Fetch Across Commit 패턴 수정

PL/SQL 코드에서 커서 루프 중 COMMIT을 수행하는 잘못된 패턴을 수정합니다.

-- 문제가 있는 패턴 (ORA-01555 유발 가능)
DECLARE
    CURSOR c1 IS SELECT * FROM orders WHERE status = 'PENDING';
BEGIN
    FOR rec IN c1 LOOP
        UPDATE orders SET status = 'PROCESSED' WHERE order_id = rec.order_id;
        COMMIT; -- 커서 열린 상태에서 커밋 → ORA-01555 위험
    END LOOP;
END;
/

-- 개선된 패턴 1: BULK COLLECT + FORALL 사용
DECLARE
    TYPE t_orders IS TABLE OF orders%ROWTYPE;
    l_orders t_orders;
BEGIN
    SELECT * BULK COLLECT INTO l_orders
    FROM orders WHERE status = 'PENDING';

    FORALL i IN 1..l_orders.COUNT
        UPDATE orders SET status = 'PROCESSED'
        WHERE order_id = l_orders(i).order_id;

    COMMIT; -- 모든 처리 후 한 번만 커밋
END;
/

-- 개선된 패턴 2: ROWID 기반 배치 처리
DECLARE
    CURSOR c1 IS SELECT ROWID AS rid FROM orders WHERE status = 'PENDING';
    TYPE t_rowid IS TABLE OF ROWID;
    l_rowids t_rowid;
    l_batch_size NUMBER := 1000;
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 BULK COLLECT INTO l_rowids LIMIT l_batch_size;
        EXIT WHEN l_rowids.COUNT = 0;

        FORALL i IN 1..l_rowids.COUNT
            UPDATE orders SET status = 'PROCESSED'
            WHERE ROWID = l_rowids(i);

        COMMIT;
    END LOOP;
    CLOSE c1;
END;
/

4. Undo 사용량 모니터링 및 분석

ORA-01555 발생 빈도와 원인을 분석하기 위한 조회 쿼리입니다.

-- Undo Segment 상태 확인
SELECT usn, name, status, xacts, gets, waits, writes
FROM v$rollstat rs, v$rollname rn
WHERE rs.usn = rn.usn
ORDER BY writes DESC;

-- 최근 ORA-01555 발생 이력 확인 (Alert Log 기반)
SELECT *
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01555%'
ORDER BY originating_timestamp DESC;

-- Undo 보존 통계 확인 (얼마나 만료됐는지)
SELECT undoblks, maxquerylen, ssolderrcnt, nospaceerrcnt
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Undo Tablespace 사용률 확인
SELECT a.tablespace_name,
       ROUND((1 - (b.free / a.total)) * 100, 2) AS used_pct,
       ROUND(a.total / 1024 / 1024 / 1024, 2) AS total_gb,
       ROUND(b.free / 1024 / 1024 / 1024, 2) AS free_gb
FROM (SELECT tablespace_name, SUM(bytes) AS total
      FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) AS free
      FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
  AND a.tablespace_name = 'UNDOTBS1';

---

예방 방법

1. Undo Tablespace 사이즈 자동 계산 및 적절한 크기 유지

Oracle에서는 필요한 Undo Tablespace 크기를 아래 공식으로 산정할 수 있습니다. 이를 주기적으로 검토하여 환경 변화에 대응해야 합니다.

-- 권장 Undo Tablespace 크기 산정 공식
-- 필요 크기(bytes) = UPS × UNDO_RETENTION + DB_BLOCK_SIZE × OVERHEAD
-- UPS = Undo Blocks Per Second (v$undostat에서 확인)

SELECT MAX(undoblks) / 600 AS undo_blocks_per_sec,
       MAX(undoblks) / 600 * (SELECT value FROM v$parameter WHERE name = 'undo_retention') *
       (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 / 1024
       AS recommended_undo_gb
FROM v$undostat;

운영 환경에서는 Undo Tablespace에 AUTOEXTEND를 반드시 설정하고, 임계치(80% 이상) 도달 시 자동 알림이 발송되도록 모니터링 체계를 구축하세요.

2. 장시간 쿼리에 대한 쿼리 튜닝 및 분할 처리

단일 쿼리로 수천만 건을 처리하는 배치 작업은 반드시 배치 단위(Chunk)로 분할하여 처리하도록 설계해야 합니다. 또한 실행 계획을 정기적으로 점검하여 Full Table Scan이 불필요하게 발생하지 않도록 인덱스를 최적화하고, 야간 배치와 OLTP 쿼리의 실행 시간대를 분리하는 운영 정책도 중요합니다.

---

관련 에러

• ORA-01562: Undo Segment를 확장하는 데 실패했을 때 발생하며, Undo Tablespace 공간 부족 시 ORA-01555와 함께 나타나는 경우가 많습니다.
• ORA-30036: Undo Tablespace에 공간이 부족하여 Undo Segment를 확장할 수 없을 때 발생합니다. ORA-01555의 전조 증상으로 나타날 수 있습니다.
• ORA-01628: 특정 Undo Segment의 최대 익스텐트 수에 도달했을 때 발생하며, 역시 Undo 관리 부실과 관련됩니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.