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

ORA-12054
2026년 09월 06일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-12054 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-12054 cannot set the ON COMMIT refresh attribute for the materialized view 는?

ORA-12054 에러는 Materialized View(구체화된 뷰)를 생성하거나 변경할 때 ON COMMIT 갱신 옵션을 설정하려 했지만, 해당 Materialized View의 구조나 쿼리가 ON COMMIT 방식의 자동 갱신을 지원하지 않는 경우에 발생합니다. ON COMMIT 갱신은 기본 테이블에 DML(INSERT, UPDATE, DELETE) 작업이 수행되고 COMMIT이 실행될 때마다 Materialized View를 자동으로 최신 상태로 유지하는 기능입니다. 이 에러는 특히 복잡한 쿼리(GROUP BY, DISTINCT, 조인, 서브쿼리 등)를 포함하는 Materialized View에서 자주 발생하며, Oracle이 해당 조건에서 ON COMMIT 갱신을 지원할 수 없다고 판단할 때 나타납니다.

주요 발생 원인

1. Materialized View Log(MV Log)가 없거나 잘못 설정된 경우

ON COMMIT 방식의 FAST REFRESH(빠른 갱신)를 사용하기 위해서는 반드시 기본 테이블에 Materialized View Log가 미리 생성되어 있어야 합니다. MV Log가 존재하지 않거나, MV Log에 필요한 컬럼 또는 옵션(예: INCLUDING NEW VALUES, SEQUENCE)이 누락된 경우 Oracle은 ON COMMIT 갱신을 수행할 수 없어 ORA-12054 에러를 발생시킵니다. 이 경우 ON COMMIT과 COMPLETE REFRESH를 함께 사용하는 것도 제약이 따릅니다.

2. Materialized View 쿼리가 ON COMMIT FAST REFRESH 조건을 만족하지 못하는 경우

Oracle의 ON COMMIT 갱신, 특히 FAST REFRESH는 매우 엄격한 쿼리 제약 조건을 가지고 있습니다. 서브쿼리, CONNECT BY, UNION/UNION ALL, 분석 함수(RANK, ROW_NUMBER 등), DISTINCT, 특정 형태의 GROUP BY 등이 포함된 쿼리는 ON COMMIT FAST REFRESH를 지원하지 않습니다. 이러한 복잡한 쿼리 구조를 사용하면서 동시에 ON COMMIT 옵션을 지정하면 에러가 발생합니다.

3. ON COMMIT COMPLETE REFRESH 자체의 제약

REFRESH COMPLETE ON COMMIT 옵션은 Oracle 버전에 따라 지원 범위가 다르며, 특히 원격 테이블(DB Link를 통한 테이블)이 포함된 경우, 또는 Materialized View 내부에 특정 객체 타입이나 제약 조건이 있는 경우 ON COMMIT 방식 자체를 허용하지 않습니다. 일부 버전에서는 ON COMMIT COMPLETE REFRESH가 아예 지원되지 않으며, 이 경우 ON DEMAND 또는 스케줄 기반의 갱신으로 전환해야 합니다.

해결 방법

원인 1 해결: Materialized View Log 생성 및 확인

먼저 기본 테이블에 MV Log가 제대로 생성되어 있는지 확인하고, 없다면 생성합니다.

-- MV Log 존재 여부 확인
SELECT log_owner, master, log_table, rowids, primary_key, sequence, include_new_values
FROM dba_mview_logs
WHERE master = 'SALES'; -- 기본 테이블명으로 변경

-- MV Log 생성 (ON COMMIT FAST REFRESH에 필요한 옵션 포함)
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (sale_id, product_id, amount)
INCLUDING NEW VALUES;

-- 이후 ON COMMIT Materialized View 생성
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FAST ON COMMIT
AS
SELECT product_id, SUM(amount) AS total_amount, COUNT(*) AS cnt
FROM sales
GROUP BY product_id;

원인 2 해결: 쿼리 구조 단순화 또는 갱신 방식 변경

복잡한 쿼리를 사용해야 한다면 ON COMMIT 대신 ON DEMAND 또는 START WITH ... NEXT ... 스케줄 방식을 사용합니다.

-- 복잡한 쿼리를 포함하는 경우: ON DEMAND + COMPLETE REFRESH 사용
CREATE MATERIALIZED VIEW mv_complex_report
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT d.department_name,
       e.job_id,
       COUNT(e.employee_id) AS emp_count,
       AVG(e.salary) AS avg_salary,
       RANK() OVER (PARTITION BY d.department_name ORDER BY AVG(e.salary) DESC) AS salary_rank
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, e.job_id;

-- 주기적 갱신을 위한 DBMS_SCHEDULER 잡 생성
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'REFRESH_MV_COMPLEX_REPORT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''MV_COMPLEX_REPORT'', ''C''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled         => TRUE,
    comments        => 'Hourly refresh for mv_complex_report'
  );
END;
/

원인 3 해결: COMPLETE REFRESH ON COMMIT 제약 우회

원격 테이블이나 복잡한 조인이 포함된 경우, ON COMMIT 대신 짧은 주기의 스케줄 갱신으로 대체합니다.

-- 원격 테이블 포함 MV: ON DEMAND 사용
CREATE MATERIALIZED VIEW mv_remote_orders
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders@remote_db_link
WHERE order_date >= ADD_MONTHS(SYSDATE, -3);

-- 수동 갱신 방법
EXEC DBMS_MVIEW.REFRESH('MV_REMOTE_ORDERS', 'C');

-- 여러 MV를 한 번에 갱신하는 방법
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS(number_of_failures => :x);

-- 갱신 가능 여부 사전 확인 (ON COMMIT FAST REFRESH 가능 여부 체크)
DECLARE
  v_msg VARCHAR2(4000);
BEGIN
  DBMS_MVIEW.EXPLAIN_MVIEW(
    mv => 'SELECT product_id, SUM(amount) FROM sales GROUP BY product_id',
    msg => v_msg
  );
  DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

예방 방법

1. Materialized View 생성 전 EXPLAIN_MVIEW로 사전 검증

Materialized View를 실제로 생성하기 전에 반드시 DBMS_MVIEW.EXPLAIN_MVIEW 프로시저를 사용하여 해당 쿼리가 원하는 갱신 방식(ON COMMIT, FAST 등)을 지원하는지 사전에 확인해야 합니다. 이 프로시저는 MV_CAPABILITIES_TABLE 테이블에 결과를 기록하므로, 해당 테이블을 조회하면 어떤 제약 조건 때문에 특정 갱신 방식이 불가능한지 상세히 파악할 수 있습니다.

-- 사전 검증 테이블 생성 (최초 1회)
@$ORACLE_HOME/rdbms/admin/utlxmv.sql

-- EXPLAIN_MVIEW로 갱신 가능 여부 분석
BEGIN
  DBMS_MVIEW.EXPLAIN_MVIEW(
    mv => 'SELECT product_id, SUM(amount) AS total FROM sales GROUP BY product_id'
  );
END;
/

-- 결과 확인
SELECT capability_name, possible, related_text, msgtxt
FROM mv_capabilities_table
ORDER BY seq;

2. MV Log 표준화 및 설계 가이드라인 수립

새로운 Materialized View를 설계할 때부터 ON COMMIT FAST REFRESH 조건(단순 조인, GROUP BY, 집계 함수 위주)을 고려하여 쿼리를 작성하고, 기본 테이블에는 항상 표준화된 MV Log를 함께 생성하는 절차를 팀 내 DBA 표준으로 정착시켜야 합니다. 또한 복잡한 리포팅 쿼리는 처음부터 ON DEMAND + SCHEDULER 방식으로 설계하고, 실시간성이 중요한 단순 집계 MV만 ON COMMIT을 적용하는 구분 전략을 수립하면 이 에러를 사전에 방지할 수 있습니다.