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

ORA-12004 REFRESH FAST cannot be used for materialized view 는?

ORA-12004 에러는 Materialized View(MV)를 FAST 방식으로 새로고침(REFRESH)하려 할 때, 해당 MV가 FAST REFRESH를 지원하지 않는 구조로 정의되어 있을 경우 발생하는 에러입니다. FAST REFRESH는 변경된 데이터만 증분 방식으로 반영하는 효율적인 방법이지만, 이를 사용하려면 Materialized View Log(MV Log)가 사전에 생성되어 있어야 하고, MV 쿼리 구조 자체도 특정 조건을 만족해야 합니다. 실무에서는 MV Log 누락, 복잡한 쿼리 사용, 또는 잘못된 REFRESH 옵션 설정 등으로 인해 이 에러가 자주 발생하며, 데이터 동기화 작업 중단으로 이어져 운영에 심각한 영향을 줄 수 있습니다.

---

주요 발생 원인

1. Materialized View Log(MV Log)가 생성되지 않은 경우

FAST REFRESH의 핵심 전제 조건은 원본 테이블에 Materialized View Log가 반드시 존재해야 한다는 것입니다. MV Log는 원본 테이블의 변경 사항(INSERT, UPDATE, DELETE)을 추적하는 로그 테이블로, 이것이 없으면 Oracle은 어떤 데이터가 변경되었는지 알 수 없어 FAST REFRESH를 수행할 수 없습니다. 특히 여러 테이블을 조인하는 MV의 경우, 조인에 참여하는 모든 테이블에 MV Log가 있어야 합니다.

2. Materialized View 쿼리 구조가 FAST REFRESH를 지원하지 않는 경우

FAST REFRESH는 모든 SQL 구조를 지원하지 않습니다. DISTINCT, GROUP BY, CONNECT BY, UNION, UNION ALL, MINUS, INTERSECT, 서브쿼리, 분석 함수(Analytic Functions), 외부 조인(Outer Join) 등 특정 SQL 구문이 포함된 경우 FAST REFRESH가 불가능합니다. 쿼리가 복잡할수록 FAST REFRESH 제약 조건을 위반할 가능성이 높아지므로, MV 설계 단계에서 반드시 FAST REFRESH 가능 여부를 검토해야 합니다.

3. Materialized View가 FAST REFRESH 옵션 없이 생성된 경우

MV 생성 시 REFRESH FAST 또는 REFRESH FAST ON COMMIT / ON DEMAND 옵션을 명시하지 않거나, 잘못된 옵션으로 생성한 경우에도 이 에러가 발생합니다. 예를 들어 REFRESH COMPLETE로 생성된 MV에 대해 수동으로 FAST REFRESH를 시도하면 에러가 발생합니다. 또한 이미 생성된 MV의 REFRESH 방식을 변경하려 할 때도 주의가 필요합니다.

---

해결 방법

원인 1 해결: MV Log 생성

원본 테이블에 Materialized View Log를 생성합니다. 조인 MV의 경우 모든 원본 테이블에 MV Log를 생성해야 합니다.

-- 단일 테이블에 MV Log 생성
CREATE MATERIALIZED VIEW LOG ON scott.emp
WITH ROWID, SEQUENCE (empno, ename, sal, deptno)
INCLUDING NEW VALUES;

-- 조인 MV의 경우 조인 대상 테이블 모두에 MV Log 생성
CREATE MATERIALIZED VIEW LOG ON scott.dept
WITH ROWID, SEQUENCE (deptno, dname, loc)
INCLUDING NEW VALUES;

-- MV Log 생성 확인
SELECT log_owner, master, log_table, rowids, sequence, include_new_values
FROM dba_mview_logs
WHERE master IN ('EMP', 'DEPT');

원인 2 해결: MV 쿼리 구조 검토 및 FAST REFRESH 가능 여부 확인

MV 생성 전에 DBMS_MVIEW.EXPLAIN_MVIEW 프로시저를 활용하여 FAST REFRESH 가능 여부를 사전에 검증합니다.

-- EXPLAIN_MVIEW 결과를 저장할 테이블 생성 (최초 1회)
-- $ORACLE_HOME/rdbms/admin/utlxmv.sql 스크립트 실행 필요
@$ORACLE_HOME/rdbms/admin/utlxmv.sql

-- FAST REFRESH 가능 여부 분석
BEGIN
  DBMS_MVIEW.EXPLAIN_MVIEW(
    mv => 'SELECT e.empno, e.ename, e.sal, d.dname
           FROM scott.emp e, scott.dept d
           WHERE e.deptno = d.deptno',
    stmt_id => 'TEST_MV_01'
  );
END;
/

-- 분석 결과 확인
SELECT capability_name, possible, related_text, msgno, msgtxt
FROM mv_capabilities_table
WHERE statement_id = 'TEST_MV_01'
  AND capability_name IN ('REFRESH_FAST', 'REFRESH_FAST_AFTER_INSERT',
                          'REFRESH_FAST_AFTER_ONETAB_DML',
                          'REFRESH_FAST_AFTER_ANY_DML')
ORDER BY seq;

-- FAST REFRESH 불가능한 쿼리 예시 (DISTINCT 사용으로 인한 제약)
-- 아래는 FAST REFRESH 불가
CREATE MATERIALIZED VIEW mv_bad_example
REFRESH FAST ON DEMAND
AS
SELECT DISTINCT deptno FROM scott.emp; -- DISTINCT는 FAST REFRESH 불가

-- FAST REFRESH 가능한 쿼리로 변경
CREATE MATERIALIZED VIEW mv_good_example
REFRESH FAST ON DEMAND
AS
SELECT deptno, COUNT(*) AS emp_count
FROM scott.emp
GROUP BY deptno;

원인 3 해결: MV를 FAST REFRESH 옵션으로 재생성

기존 MV를 DROP하고 FAST REFRESH 옵션으로 다시 생성합니다.

-- 기존 MV 삭제
DROP MATERIALIZED VIEW mv_emp_dept;

-- MV Log가 정상적으로 존재하는지 확인
SELECT log_owner, master, log_table
FROM dba_mview_logs
WHERE master IN ('EMP', 'DEPT');

-- FAST REFRESH 옵션으로 MV 재생성
CREATE MATERIALIZED VIEW mv_emp_dept
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT e.empno,
       e.ename,
       e.sal,
       e.deptno,
       d.dname,
       d.loc
FROM scott.emp e, scott.dept d
WHERE e.deptno = d.deptno;

-- ON COMMIT 방식으로 자동 갱신하는 경우
CREATE MATERIALIZED VIEW mv_emp_dept_commit
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT e.empno,
       e.ename,
       e.sal,
       d.dname
FROM scott.emp e, scott.dept d
WHERE e.deptno = d.deptno;

-- 수동으로 FAST REFRESH 실행 (ON DEMAND)
BEGIN
  DBMS_MVIEW.REFRESH(
    list          => 'MV_EMP_DEPT',
    method        => 'F',   -- F: FAST, C: COMPLETE, A: ALWAYS FAST
    atomic_refresh => FALSE
  );
END;
/

-- COMPLETE REFRESH로 대체 (FAST가 불가능한 경우 임시 방편)
BEGIN
  DBMS_MVIEW.REFRESH(
    list          => 'MV_EMP_DEPT',
    method        => 'C',   -- C: COMPLETE REFRESH
    atomic_refresh => FALSE
  );
END;
/

-- MV 현재 상태 및 REFRESH 방식 확인
SELECT mview_name,
       refresh_mode,
       refresh_method,
       last_refresh_type,
       last_refresh_date,
       staleness
FROM dba_mviews
WHERE mview_name = 'MV_EMP_DEPT';

---

예방 방법

1. MV 생성 전 EXPLAIN_MVIEW를 통한 사전 검증 프로세스 의무화

모든 Materialized View 생성 전에 반드시 DBMS_MVIEW.EXPLAIN_MVIEW를 실행하여 FAST REFRESH 가능 여부를 확인하는 것을 개발 프로세스에 포함시켜야 합니다. MV Log 생성 → EXPLAIN_MVIEW 검증 → MV 생성의 3단계 프로세스를 표준화하고, CI/CD 파이프라인이나 배포 스크립트에 이 검증 단계를 자동화하면 운영 환경에서의 에러 발생을 사전에 차단할 수 있습니다.

2. 정기적인 MV 상태 모니터링 및 알림 체계 구축

DBA_MVIEWS 뷰의 STALENESS 컬럼과 LAST_REFRESH_DATE를 주기적으로 모니터링하여 MV가 최신 상태인지 확인하는 모니터링 스크립트를 구성하고, MV REFRESH 실패 시 즉시 담당자에게 알림이 발송되도록 Oracle Scheduler Job이나 외부 모니터링 도구와 연동해야 합니다. 또한 MV Log가 과도하게 쌓이지 않도록 MV Log 크기를 주기적으로 점검하고, 불필요한 MV Log는 정리하는 유지보수 작업도 함께 수행해야 합니다.

---

관련 에러

• ORA-12000: Materialized View Log가 이미 존재할 때 중복 생성 시도 시 발생
• ORA-12001: MV Log의 컬럼이 변경되거나 누락되었을 때 발생
• ORA-12054: ON COMMIT REFRESH가 지원되지 않는 MV 구조에서 발생
• ORA-23413: 테이블에 MV Log가 존재하지 않을 때 발생하는 관련 에러
• ORA-32401: MV Log에 SEQUENCE 옵션 없이 특정 REFRESH를 시도할 때 발생

---

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