Fast Refresh on commit of materialized view

I don't know if the problem still persists, but as I took a look at the artice you provided, I noticed something (which might just be the solution here):

ON COMMIT Refresh

A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view. The only disadvantage is the time required to complete the commit will be slightly longer because of the extra processing involved. However, in a data warehouse, this should not be an issue because there is unlikely to be concurrent processes trying to update the same table.

• Notice the bold line.

Then we have:

Table 7-1 ON DEMAND Refresh Methods

Refresh Option Parameter DescriptionCOMPLETE C Refreshes by recalculating the defining query of the materialized view.

FAST F Refreshes by incrementally applying changes to the materialized view. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.

FAST_PCT P Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.

FORCE ? Attempts a fast refresh. If that is not possible, it does a complete refresh.For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

• Notice the bold lines.
• I personally prefer the FORCE Option.

Could you please tell, if this occurs again after some time (depending of the parameters of the DB and the machine it runs on, so I can't even hint you how much)?

When Fast Refresh is Possible

Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view.

If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which provides a script containing the statements required to create a fast refreshable materialized view.

Cheers

I see that you created the materialized view logs with ROWID, which is not really required as both tables have a primary key so you could try without the ROWID.

create materialized view log on emp;create materialized view log on dept;

Additionally, if you create the materialized view log with ROWID you should create the materialized view with rowid.

create materialized view empdept_mv refresh fast on commit WITH ROWID asselect a.rowid dept_rowid, b.rowid emp_rowid, a.dept_no,b.emp_nofrom dept a, emp bwhere a.dept_no=b.dept_no ;

You could try those changes and see if the materialized views fast refresh on commit.