One of the uses of materialized views is replication.  Oracle Materialized Views can be used to replicate a table from the master database to another database to prevent users from accessing several databases through database links. This can improve the performance of queries which frequently access that table by removing the latency of the database link and also enhance security policies.

Yesterday out refresh job of one of the mviews we use for this purpose started getting “ORA-00001 unique constraint violated” error. This was an mview with a unique index on it.  Not to say that there are others unique index in the same MV.

SQL> exec dbms_mview.refresh(”METER_INVENTORY”);
BEGIN dbms_mview.refresh(”METER_INVENTORY”); END;
*
ERROR at line 1:
ORA-12008: error in snapshot refresh path
ORA-00001: unique constraint (ISDA.METER_INV_MII_MICC_MITC_IDX) violated
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 617
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 674
ORA-06512: at “SYS.DBMS_IREFRESH”, line 577
ORA-06512: at “SYS.DBMS_REFRESH”, line 211
ORA-06512: at line 1

If we drop the unique index and recreate it as nonunique we can refresh the mview without errors.

SQL>DROP INDEX METER_INV_MII_MICC_MITC_IDX
Index dropped.

SQL>EXEC DBMS_MVIEW.REFRESH(‘METER_INVENTORY’);
PL/SQL procedure successfully completed.

SQL>COMMIT;
Commit complete.

SQL>CREATE INDEX METER_INV_MII_MICC_MITC_IDX ON ISDA.METER_INVENTORY
(MTR_INV_ID, MTR_INV_CITY_CD, MTR_INV_TYPE_CD);
Index created.

SQL>EXEC DBMS_MVIEW.REFRESH(‘METER_INVENTORY’);
PL/SQL procedure successfully completed.

The reason of this error was explained in Metalink note 284101.1. According to that note the refresh mechanism may temporarily leave the data in an inconsistent state during the refresh.

The modifications are not made with the same order as the modifications to the master table. Because of this creating unique indexes, foreign keys or primary keys on mviews can cause problems in the refresh process.

This totally makes sense as it is better to keep the integrity checks on the master table rather than the mview. An mview is just a copy of the master table, so if we define the integrity constraints on the master table the mview will take care of itself.