DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12021 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12021: Materialized View Definition is Out of Date

ORA-12021 occurs when a Materialized View's definition becomes stale due to structural changes in the underlying base objects, such as tables or views. Oracle marks the Materialized View as invalid and throws this error when any query attempts to access it. This is a common error encountered after schema migrations or DDL changes in production environments.

Top 3 Causes

1. DDL Changes on the Base Table

When you run ALTER TABLE to add, drop, or modify a column on a table that a Materialized View depends on, Oracle immediately invalidates the Materialized View definition.

-- This ALTER TABLE will invalidate dependent Materialized Views
ALTER TABLE schema_name.sales_fact ADD (discount_rate NUMBER(5,2));

-- Check which Materialized Views are now affected
SELECT owner,
       mview_name,
       compile_state,
       staleness
FROM   dba_mviews
WHERE  compile_state != 'VALID';
Enter fullscreen mode Exit fullscreen mode

2. Recompilation of Dependent Views or Objects

If a Materialized View is built on top of a regular view or another Materialized View, and that intermediate object is recompiled or invalidated, the ORA-12021 error will cascade upward through the dependency chain.

-- Check object dependencies to trace the root cause
SELECT referenced_owner,
       referenced_name,
       referenced_type,
       owner,
       name,
       type
FROM   dba_dependencies
WHERE  name  = 'MV_SALES_SUMMARY'
  AND  owner = 'SCHEMA_NAME';

-- Check overall object status
SELECT object_name,
       object_type,
       status
FROM   dba_objects
WHERE  owner       = 'SCHEMA_NAME'
  AND  object_type IN ('MATERIALIZED VIEW', 'VIEW')
  AND  status      = 'INVALID';
Enter fullscreen mode Exit fullscreen mode

3. Failed or Interrupted Refresh Jobs

A Materialized View Refresh job that fails mid-execution or is forcibly terminated can leave the Materialized View in an inconsistent state, causing ORA-12021 on the next access.

-- Check last refresh details and current state
SELECT mview_name,
       last_refresh_date,
       last_refresh_type,
       compile_state,
       staleness,
       refresh_mode
FROM   dba_mviews
WHERE  owner = 'SCHEMA_NAME'
ORDER BY last_refresh_date DESC;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1: Perform a Complete Refresh

-- Refresh a single Materialized View
BEGIN
    DBMS_MVIEW.REFRESH(
        list          => 'SCHEMA_NAME.MV_SALES_SUMMARY',
        method        => 'C',
        atomic_refresh => FALSE
    );
END;
/
Enter fullscreen mode Exit fullscreen mode

Step 2: Recompile the Materialized View

-- If refresh alone doesn't work, try recompiling
ALTER MATERIALIZED VIEW schema_name.mv_sales_summary COMPILE;
Enter fullscreen mode Exit fullscreen mode

Step 3: Drop and Recreate if Necessary

-- Backup the DDL first
SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_SALES_SUMMARY','SCHEMA_NAME')
FROM dual;

-- Drop and recreate
DROP MATERIALIZED VIEW schema_name.mv_sales_summary;

CREATE MATERIALIZED VIEW schema_name.mv_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id,
       SUM(sales_amount) AS total_sales
FROM   schema_name.sales_fact
GROUP BY dept_id;
Enter fullscreen mode Exit fullscreen mode

Step 4: Bulk fix all invalid Materialized Views

-- Generate recompile statements for all invalid MVs
SELECT 'ALTER MATERIALIZED VIEW ' || owner || '.' || mview_name || ' COMPILE;'
FROM   dba_mviews
WHERE  compile_state != 'VALID';

-- Or use UTL_RECOMP for full schema recompilation
EXEC UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always check dependencies before DDL changes and automate post-change refresh:

-- Run this BEFORE any ALTER TABLE
SELECT name, type, owner
FROM   dba_dependencies
WHERE  referenced_name  = 'SALES_FACT'
  AND  referenced_owner = 'SCHEMA_NAME'
  AND  type             = 'MATERIALIZED VIEW';
Enter fullscreen mode Exit fullscreen mode

2. Schedule regular automated Refresh jobs using DBMS_SCHEDULER:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_REFRESH_MV_DAILY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''SCHEMA_NAME.MV_SALES_SUMMARY'',''C''); END;',
        repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0',
        enabled         => TRUE
    );
END;
/
Enter fullscreen mode Exit fullscreen mode

By combining proactive dependency checks before schema changes with automated refresh scheduling and monitoring of DBA_MVIEWS.COMPILE_STATE, you can virtually eliminate ORA-12021 from your production environment.

Related Errors: ORA-12008 (error in Materialized View refresh path), ORA-32321 (MV log too old for fast refresh), ORA-12004 (fast refresh not supported), ORA-01031 (insufficient privileges during refresh).

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)