When I started my SQL-Developer today I noticed an error with my materialzed view. The Error was the well known ORA-12004: Refresh fast cannot be used...
Of course, I already used google and i came to the conclusion that a fast refresh needs the log table and my error message also said that the log table could not be accessed. BUT: I didn't use the fast refresh :/
Might the problem be, that my select sometimes contains no data? For example not for saturdays and sundays.
How I created the mat view:
CREATE MATERIALIZED VIEW Matv_test_1 BUILD IMMEDIATE REFRESH COMPLETE NEXT (trunc(sysdate) + 4/24) + 1 AS select... So what might be the problem?
Two more things concerning this: 1) Is logging enabled by default? Or how can I enable logging when I create the view? 2) Does the NEXT clause affect the refresh type or are these two options absolutely different things? For ex: In my code obove, is a refresh made at the set time only or is the refresh made after changes in my selected tables? If last one, what is made at the set time?
If i use
NEVER REFRESH NEXT (trunc(sysdate) + 4/24) + 1 is a refresh or an actualization made or not?
Thanks.
1 Answer
Logs have to created for fast refresh. it is not enabled by default. Perhaps,we have to create the logs for the tables we refer in the mv. We dont need to specify to use a particular log when creating the mv. Oracle internally takes care of using the log we have created for a table. it thows an error if the log is not enable and we are creating an mv for which logs are prerequisite like on commit, fast refresh, etc.
Next clause specifies when to refresh the mv. It creates an oracle job accordingly to perform the refresh. In your case as specified build immediate a refresh will happen immediately and the next refresh happens for the interval you specified in the next clause
If never refresh is specified we dont need to use next clause perhaps we ll come across and error i reckon
