Oracle ORA-12048 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12048: Error Encountered While Refreshing Materialized View

ORA-12048 is an Oracle error that occurs when a Materialized View (MV) refresh operation fails due to an underlying issue. It rarely appears alone — it typically surfaces alongside a child error (such as ORA-01555, ORA-00942, or ORA-04021) that reveals the true root cause. Understanding and resolving the accompanying error is the key to fixing ORA-12048.

Top 3 Causes

1. Missing Privileges or Invalid Base Objects

If the MV owner loses SELECT privileges on the base table, or if the base table (or DB Link) is dropped or altered, the refresh will fail immediately.

-- Check MV status and compile state
SELECT mview_name, staleness, compile_state, last_refresh_date
FROM dba_mviews
WHERE mview_name = 'YOUR_MV_NAME';

-- Grant missing privilege
GRANT SELECT ON schema_name.base_table TO mv_owner;

-- Test DB Link connectivity
SELECT COUNT(*) FROM remote_table@your_db_link;

-- Force a Complete Refresh after fixing privileges
BEGIN
  DBMS_MVIEW.REFRESH(
    list           => 'SCHEMA_NAME.MV_NAME',
    method         => 'C',
    atomic_refresh => FALSE
  );
END;
/

2. Undo Space Exhaustion (ORA-01555: Snapshot Too Old)

During a long-running refresh, if the Undo retention period is too short, Oracle may overwrite undo blocks needed for read consistency, causing ORA-01555 wrapped inside ORA-12048.

-- Check current UNDO_RETENTION setting
SHOW PARAMETER UNDO_RETENTION;

-- Increase UNDO_RETENTION (in seconds)
ALTER SYSTEM SET UNDO_RETENTION = 7200 SCOPE = BOTH;

-- Enable Retention Guarantee on Undo tablespace
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- Refresh with atomic_refresh=FALSE to reduce undo usage
BEGIN
  DBMS_MVIEW.REFRESH(
    list           => 'SCHEMA_NAME.MV_NAME',
    method         => 'C',
    atomic_refresh => FALSE
  );
END;
/

3. Corrupted or Missing Materialized View Log (Fast Refresh Failure)

Fast Refresh depends on the MV log. If the log is dropped, corrupted, or invalidated by a DDL change on the base table, the refresh will fail.

-- Check existing MV logs
SELECT log_owner, master, log_table, rowids, primary_key
FROM dba_mview_logs
WHERE master = 'BASE_TABLE_NAME';

-- Drop and recreate the MV log
DROP MATERIALIZED VIEW LOG ON schema_name.base_table;

CREATE MATERIALIZED VIEW LOG ON schema_name.base_table
  WITH ROWID, SEQUENCE (col1, col2, col3)
  INCLUDING NEW VALUES;

-- Fall back to Complete Refresh if Fast Refresh is not viable
BEGIN
  DBMS_MVIEW.REFRESH(
    list           => 'SCHEMA_NAME.MV_NAME',
    method         => 'C',   -- C=Complete, F=Fast, ?=Force
    atomic_refresh => FALSE
  );
END;
/

Quick Fix Checklist

Always read the full error stack — the child error below ORA-12048 tells you exactly what went wrong.
Check alert.log for detailed refresh failure messages.
Use atomic_refresh => FALSE for large MVs to avoid a single massive transaction consuming all Undo space.
Recompile invalid MVs with ALTER MATERIALIZED VIEW mv_name COMPILE; after fixing base object issues.

-- Recompile an invalid MV
ALTER MATERIALIZED VIEW schema_name.mv_name COMPILE;

-- Check all invalid MVs in the database
SELECT owner, mview_name, compile_state
FROM dba_mviews
WHERE compile_state != 'VALID';

Prevention Tips

Automate MV health checks using DBMS_SCHEDULER to query DBA_MVIEWS daily and alert when COMPILE_STATE != 'VALID'.
Size your Undo Tablespace generously with Autoextend enabled, and set RETENTION GUARANTEE to protect long-running refresh operations from ORA-01555.

Related Errors

Error Code	Description
ORA-01555	Snapshot too old — Undo exhausted during refresh
ORA-00942	Base table or view does not exist
ORA-04021	Timeout waiting to lock object during refresh
ORA-12008	General error in MV refresh path (parent of ORA-12048)

📖 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.

DEV Community