SQL> execute dbms_mview.refresh ('TX_FAIL_LOG_DAY_MV', 'f'); BEGIN DBMS_MVIEW.REFRESH ('TX_FAIL_LOG_DAY_MV', 'f'); END; * ERROR at line 1: ORA-12052: cannot fast refresh materialized view BIDATA.TX_FAIL_LOG_DAY_MV ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745 ORA-06512: at line 1
Solution: The Oracle provided DBMS_MVIEW.explain_mview procedure was used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE. Now using DBMS_MVIEW.explain_mview procedure I will analysis the possible reason why the materialized view cannot fast refresh.
SQL> EXEC dbms_mview.explain_mview('TX_FAIL_LOG_DAY_MV'); PL/SQL procedure successfully completed. SQL> SELECT capability_name, possible, substr(msgtxt,1,60) AS msgtxt FROM mv_capabilities_table WHERE capability_name like '%FAST%';
So you can see from the analysis that the log table does not have all the necessary columns. I have added the missing column. SQL> ALTER MATERIALIZED VIEW LOG ON BIDATA.TX_FAIL_LOG_HOUR_MV ADD(CONTENTPROVIDERSERVICEID);
SQL> execute dbms_mview.refresh ('TX_FAIL_LOG_DAY_MV', 'f'); BEGIN DBMS_MVIEW.REFRESH ('TX_FAIL_LOG_DAY_MV', 'f'); END; * ERROR at line 1: ORA-12052: cannot fast refresh materialized view BIDATA.TX_FAIL_LOG_DAY_MV ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745 ORA-06512: at line 1
Still we are getting the error because first we require a complete refresh. After the complete refresh fast refresh will work. SQL> execute dbms_mview.refresh ('TX_FAIL_LOG_DAY_MV', 'c'); PL/SQL procedure successfully completed. Elapsed: 02:27:28.10
SQL> execute dbms_mview.refresh ('TX_FAIL_LOG_DAY_MV', 'f'); PL/SQL procedure successfully completed. Elapsed: 00:01:38.61 |