Oracle 시퀀스 고갈 사전 모니터링과 ORA-08004 예방 SQL

요약

Oracle 시퀀스가 MAXVALUE에 도달하면 NEXTVAL 호출에서 ORA-08004가 발생해 신규 데이터 입력이 중단될 수 있습니다. alert.log에 오류가 없더라도 DBA_SEQUENCES를 주기적으로 조회해 잔여 비율과 예상 소진일을 모니터링해야 합니다.

ORA-08004의 의미

Oracle 공식 오류 설명에 따르면 ORA-08004는 다음 NEXTVAL이 시퀀스의 MINVALUE 또는 MAXVALUE를 위반할 때 발생합니다.

애플리케이션 오류로 반환되고 alert.log에 항상 기록되는 것은 아니므로 alert.log 검색만으로는 사전 예방이 어렵습니다.

기본 현황 조회

SELECT
    sequence_owner,
    sequence_name,
    min_value,
    max_value,
    increment_by,
    cycle_flag,
    cache_size,
    last_number
FROM dba_sequences
ORDER BY sequence_owner, sequence_name;

19c 문서에서 LAST_NUMBER는 디스크에 기록된 마지막 시퀀스 값입니다. CACHE 시퀀스에서는 메모리에 확보된 값 때문에 애플리케이션이 마지막으로 사용한 값과 정확히 같지 않을 수 있습니다.

사용률 계산

양수 증가 시퀀스 예시:

SELECT
    sequence_owner,
    sequence_name,
    last_number,
    max_value,
    increment_by,
    ROUND(last_number / NULLIF(max_value, 0) * 100, 4) AS used_pct,
    max_value - last_number AS remaining_value
FROM dba_sequences
WHERE increment_by > 0
  AND cycle_flag = 'N'
ORDER BY used_pct DESC;

NOMAXVALUE 성격의 매우 큰 기본 MAX_VALUE 시퀀스는 실무적으로 소진 위험이 낮을 수 있으므로 애플리케이션 컬럼의 데이터형 한계도 함께 확인합니다.

경고 대상만 조회

80% 이상 사용 또는 잔여 호출 가능 횟수가 100만 회 미만인 시퀀스:

SELECT
    sequence_owner,
    sequence_name,
    last_number,
    max_value,
    increment_by,
    ROUND(last_number / NULLIF(max_value, 0) * 100, 2) AS used_pct,
    FLOOR((max_value - last_number) / increment_by) AS remaining_calls
FROM dba_sequences
WHERE increment_by > 0
  AND cycle_flag = 'N'
  AND (
       last_number / NULLIF(max_value, 0) >= 0.80
       OR FLOOR((max_value - last_number) / increment_by) < 1000000
  )
ORDER BY used_pct DESC;

임계값은 업무 발생량과 조치 소요시간에 맞게 조정합니다.

실제 컬럼 한계 확인

시퀀스 MAXVALUE를 늘려도 대상 컬럼이 NUMBER(10) 또는 정수형 인터페이스라면 먼저 컬럼·애플리케이션 한계에 도달할 수 있습니다.

SELECT owner, table_name, column_name,
       data_type, data_precision, data_scale
FROM dba_tab_columns
WHERE owner = 'APP_OWNER'
  AND table_name = 'TARGET_TABLE';

시퀀스와 테이블의 연결 관계는 트리거, DEFAULT 절과 애플리케이션 SQL을 함께 확인해야 합니다.

증가 속도와 예상 소진일

하루 간격으로 LAST_NUMBER 스냅샷을 저장하면 일평균 증가량을 계산할 수 있습니다.

CREATE TABLE sequence_usage_history AS
SELECT SYSDATE AS snap_time,
       sequence_owner,
       sequence_name,
       last_number,
       max_value,
       increment_by
FROM dba_sequences
WHERE 1 = 0;

수집:

INSERT INTO sequence_usage_history
SELECT SYSDATE, sequence_owner, sequence_name,
       last_number, max_value, increment_by
FROM dba_sequences;
COMMIT;

RAC와 CACHE 환경에서는 인스턴스 재시작, 캐시 손실과 선할당 때문에 단기간 증분이 실제 INSERT 건수와 다를 수 있습니다. 최소 며칠 이상의 추세를 사용합니다.

스케줄러 작업 예시

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_SEQUENCE_USAGE_SNAPSHOT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      BEGIN
        INSERT INTO sequence_usage_history
        SELECT SYSDATE, sequence_owner, sequence_name,
               last_number, max_value, increment_by
        FROM dba_sequences;
        COMMIT;
      END;]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
    enabled         => TRUE
  );
END;
/

운영 적용 전 전용 모니터링 스키마, 테이블스페이스와 보존기간을 설계합니다.

조치 전 확인

SELECT DBMS_METADATA.GET_DDL(
         'SEQUENCE','SEQUENCE_NAME','OWNER'
       )
FROM dual;

확인 항목:

• 대상 테이블과 컬럼 데이터형
• 인터페이스와 프로그램의 정수 범위
• RAC 인스턴스별 사용 방식
• ORDER, CACHE, CYCLE 설정
• 복제·GoldenGate와 데이터 충돌 가능성
• 변경 시 애플리케이션 영향

MAXVALUE 변경 예시

ALTER SEQUENCE APP_OWNER.ORDER_SEQ MAXVALUE 999999999999999999;

변경 전 애플리케이션과 컬럼이 확장된 값을 처리할 수 있는지 검증해야 합니다. CYCLE로 단순 전환하면 기존 키와 중복될 수 있으므로 기본키 시퀀스에는 특히 위험합니다.

운영자 체크리스트

• alert.log 외에 DBA_SEQUENCES를 모니터링하는가?
• CACHE로 인한 LAST_NUMBER 차이를 이해했는가?
• 시퀀스와 실제 컬럼의 연결 관계를 확인했는가?
• 데이터형과 외부 인터페이스 한계를 확인했는가?
• 증가 추세를 며칠 이상 수집했는가?
• 80%, 90%, 95% 단계별 경보를 설정했는가?
• CYCLE 전환 시 중복 위험을 평가했는가?
• 변경 DDL과 롤백 계획을 검토했는가?

정리

ORA-08004는 발생 후 대응하면 INSERT 장애로 이어질 수 있으므로 시퀀스 사용률과 증가 속도를 미리 모니터링해야 합니다. 특히 alert.log에 오류가 없다는 이유로 안전하다고 판단하지 말고 DBA_SEQUENCES, 컬럼 데이터형과 실제 업무 증가량을 함께 확인합니다.

참고자료

• Oracle – ORA-08004
• Oracle Database 19c – DBA_SEQUENCES
• Oracle Database 19c – CREATE SEQUENCE