ORA-12008: error in materialized view refresh path

This was just one of those days, when strange things happen. Consider this:

You have a materialized view with left outer join:

CREATE MATERIALIZED VIEW table_mw
      REFRESH FAST ON COMMIT
      WITH ROWID
      AS
SELECT
      v.ROWID v_rid,
      t.ROWID t_rid,
      s.ROWID s_rid,
      v.*,
      s.dfr,
      s.dfr
FROM
      table_v v,
      table_t t,
      table_s s
WHERE v.ref_id = t.id (+)
      AND v.ref_id = s.id (+)
/

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID
/

Now, let’s keep aside that not only you have to comply with documented rules for view to be fast refreshable (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1006674), there is also one undocumented restriction: ANSI-joins are not supported.

With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted to commit. Commit returned following error stack:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

Cause:  Table SNAP$_<mview_name> reads rows from the view MVIEW$_<mview_name>, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table <master_owner>.MLOG$_<master> is also referenced.

Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> still exist.

I checked alert log and trace files to find nothing of use there. After some experimentation I found out that for some reason, Oracle cannot cope with timestamp-based materialized view logs. Implementation of commit SCN-based materialized view logs solved the problem.

So in the end materialized view logs looked like this:

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID, COMMIT SCN
/

Seems like one of those problems you solve, but don’t know why they came in first place.

PS: There is also one important think you should not forget or you'll get same generic error on COMMIT of one of the source tables. The thing is that if you materialized view is in different schema, don't forget to also GRANT SELECT on materialized view logs (MLOG$, RUPD$).