ORA-12052: cannot fast refresh materialized view

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