I'm trying to run a fast refresh for the MV. The complete refresh works fine, but the fast refresh gives an error.
When I run the query :
exec DBMS_SNAPSHOT.REFRESH( '"DWADM"."B1_RST_CALL_MON_MV1"','F');
I get an error stated below -
Error report -
ORA-12033: cannot use filter columns from materialized view log on "DWADM"."CD_CTRL_ZONE"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1
12033. 0000 - "cannot use filter columns from materialized view log on \"%s\".\"%s\""
*Cause: The materialized view log either did not have filter columns
_logged, or the timestamp associated with the filter columns was_
_more recent than the last refresh time._
*Action: A complete refresh is required before the next fast refresh.
_Add filter columns to the materialized view log, if required._
The CD_CTRL_ZONE log table has all the filter columns being used in the MV. To dig deeper into the error. I also ran:
EXECUTE dbms_mview.explain_mview('DWADM.B1_RST_CALL_MON_MV1');
SELECT capability_name, possible, SUBSTR(msgtxt,1,100) AS msgtxt, RELATED_TEXT, MVNAME
_FROM mv\_capabilities\_table_
_WHERE capability\_name like '%FAST%';_
But this too gives a similar message of missing columns in log of CD_CTRL_ZONE :
