Skip to content

How to Resolve ORA-12008: error in materialized view refresh path

This error means something prevent the snapshots from reading the master tables when refreshing the snapshots.

The possible causes could be:
  • Columns have been added into the master table:
  • In most cases, you know who did it without notifying DBA.
    You should: recreate the snapshot.
  • The master table has been dropped:
  • If the dropping is planned.
    You should: drop the snapshot.
  • Database link is unavailable.
  • The accounts of database links could be locked, altered or temporarily unreachable due to network problems.
    You should: check the availability of the database link, including account status and network availability.
  • Unique constraint violated.
  • The index violation happened on the snapshot, not on the master table.
    You should: rebuild the index.
The accompanied error in the stack should provide you more information about the root cause.

I summarized the solutions as below:
  • Make a complete refresh: If the master table is quite small.
  • Rebuild the unique index of the snapshot: If the master table is quite large.
  • Rereate the snapshot: If DDL of the master table is altered or you had tried all above methods but failed.
  • Drop the snapshot: If the master table is no longer existent.

4 thoughts on “How to Resolve ORA-12008: error in materialized view refresh path”

  1. I create snapshot withou the lob column but after refresh i find the lob is included and after more refreshes i get th ora-12008
    Please help

  2. I had a column which was a char(1) being converted to a number in the MV SELECT statement. That was enough to blow it up when trying to refresh it.

    You can see the invalid number reported here.

    ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2370
    ORA-01722: invalid number
    ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 85
    ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 245

    That’s what I get for not testing the SQL outside of the MV create script.

Leave a Reply

Your email address will not be published.