Found an error ORA-12034 in alert log:
ORA-12034: materialized view log on younger than last refresh
ORA-12034 means that your last refresh older than the materialized view log and no way to sync back to the master table due to some reasons. After doing some researches, I found there're several possibilities that can cause the problem:
- The definition of the master table has been changed. e.g. Columns added.
- The segment(s) of the master table was reorganized. (rowid will be changed as well)
- The last refresh failed for some reasons.
If you are able to do complete refresh, but unable to do fast refresh. I think the major problem is on the materialized view log.
Therefore, we should recreate the materialized view log in order to solve the problem.
If the recreation of MV log can't solve ORA-12034, try to recreate the materialized view with IMMEDIATE or ON PREBUILT TABLE which depends on your situation. If you'd like to make sure whether to use ON PREBUILT TABLE or not in the syntax of creating the MV, you can find out the build mode by the statement below:
SQL> select build_mode from dba_mviews where owner = 'OWNER' and mview_name = 'MVIEW_NAME';
More syntax variations to create materialized view can be found:
CREATE MATERIALIZED VIEW - Oracle 12c Official Documentation
I have to note this down: There's another type of error messages that you could meet in the process of debugging. Same problem, same resolution (that is, recreate the MV log).
ORA-12018: following error encountered during code generation for "OWNER"."MVIEW_NAME"
ORA-00942: table or view does not exist