We have seen the ORA-12052 a few times in the previous post, but this time it's because d.rowid is missing from the select list of the materialized view. For some reason, I usually forget adding the rowid when adding a table to the from-clause. So I am very familiar with this particular situation.

3) They cannot have GROUP BY clauses or aggregates.

This situation overlaps a bit with the next post's topic: aggregate MV's. Let's just add a dummy group by clause:

And again it works. This restriction doesn't seem to be a restriction. But maybe it breaks when adding some more complexity. I certainly wouldn't base a production system on it, after having been warned by the documentation.

4) Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

First restore the myemp materialized view log and then drop the mydept view log:

Only difference with the above mentioned 5 restrictions, is the fact that yours are undocumented restrictions (read: bugs). And so they should work and maybe they will in some future version/patch set.

The ANSI join one looks very common though, and I've updated the post to reflect this situation as well.

Oracle's documentation (10gR2) for "plain vanilla" materialized views is indeed excellent, but as you've mentioned, once you try to do anything elaborate, the documents start becoming convoluted and even self-contradictory. (One page will say that you can't perform a Fast Refresh if the mview's defining query contains a UNION ALL, another page will say that you can, but only if A, B, and C are true...)

Oracle's debugging tools (e.g. DBMS_MVIEW.EXPLAIN_MVIEW) are really well-done, but also fall short when you move from simplicity to complexity.

The worst part of the whole situation is that it's difficult to tell from the documentation whether the process you're trying to implement is even supported. You have to poke, prod, and tweak various settings until you hit that nirvana-like state where Oracle finally gives up and says "Okay, I'll let you Fast Refresh now".

The whole process is fun, after a fashion, but it makes one wonder whether they're nursing a subconscious masochistic streak...

I had followed all the above restriction while creating Materialized view with refresh fast on COMMIT option, but still getting same error message ORA-12054: cannot set the on commit refresh attribute for the materialized view

My guess is that general restriction 7 (It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.) kicks in with your predicate "thrt.catalogue_change_id = (select ...)". You can check by removing that predicate and replacing all sysdates with a fixed date. If that still doesn't work, my advice is to post your question on OTN or Stack Overflow.