Create Materialized view which refresh records on daily
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 + 1as 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
If you simply need a SQL query to simply refresh at 12 AM
, then the below query would be enough.
CREATE MATERIALIZED VIEW MV_DATABUILD IMMEDIATE REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) WITH ROWIDON COMMITDISABLE QUERY REWRITEAS SELECT * FROM <YOUR TABLE>
If you need to have it refreshed around 6 AM
, then use the below script. You can see and additional logic as + 6 / 24
. In case if you need to change to 4 AM
, use the logic as + 4 / 24
.
CREATE MATERIALIZED VIEW MV_DATABUILD IMMEDIATE REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) + 6 / 24 WITH ROWIDON COMMITDISABLE QUERY REWRITEAS SELECT * FROM <YOUR TABLE>
I have edited Sarath's Script for it to run on specific time (i.e. 6AM).
CREATE MATERIALIZED VIEW MV_DATABUILD IMMEDIATE REFRESH FAST START WITH (SYSDATE) NEXT (TRUNC(SYSDATE) + 1) + 6 / 24 WITH ROWIDON COMMITDISABLE QUERY REWRITEAS SELECT * FROM YOURTABLE