Currently the Materialized view which I had created using REFRESH ON DEMAND so in this case I need to refresh MV explicitly using below command:
BEGIN DBMS_MVIEW.REFRESH('MV_DATA'); END;
But now I need to refresh this MV on daily basis so could anyone please help to write this. I have seen that we can refresh this MV using writing explicit Job or using COMPLETE/FAST REFRESH statement in MV itself.
Thanks in advance!
You need to create the materialized view using START WITH and NEXT Clause
create materialized view <mview_name>
refresh on demand
start with sysdate next sysdate + 1
as select ............
So if you want to refresh mview daily, you need to keep it refresh on demand and set the next refresh time as sysdate + 1
. You can set any interval although.
Once you do this the materialized view is created and a job is set in Oracle that will refresh mview every 24 hrs (sysdate + 1)
.
For more information on how to do that, follow this link