Create Materialized view which refresh records on daily

Question

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!

So if you have seen the ways MV can be refreshed, what is your exact problem? — Yaroslav Shabalin, Mar 18 '14 at 14:22
@YaroslavShabalin: I need to do this automate refresh instead of doing this manually — cool_taps, Mar 19 '14 at 03:18

San · Accepted Answer · 2021-08-11T13:43:49.563

22

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

edited Aug 11 '21 at 13:43

answered Mar 18 '14 at 14:28

San

4,508
1
13
19

2

if it is scheduled to be refreshed automatically then why on demand ? – donstack Jan 02 '15 at 12:05
3

`On Demand` is default mode and scheduled refresh(automatic) is also part of `on demand` mode. Apart from that you can mention `on commit` where mview will be refreshed when DML happens on underlying tables and commit is fired. – San Jan 04 '15 at 17:12

score 4 · Answer 2 · edited Aug 30 '21 at 20:50

4

If you simply need a SQL query to simply refresh at 12 AM, then the below query would be enough.

CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS 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_DATA
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) + 6 / 24 WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM <YOUR TABLE>

edited Aug 30 '21 at 20:50

BIBD

15,107
25
85
137

answered Apr 09 '17 at 21:15

Sarath Subramanian

20,027
11
82
86

1

IMMEDIATE not IMMEDEATE – Makatun Jun 01 '17 at 17:54
How would you do 7:30? – ShreyasD Nov 07 '18 at 03:11

score 0 · Answer 3 · edited Aug 30 '21 at 20:50

0

I have edited Sarath's Script for it to run on specific time (i.e. 6AM).

CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (TRUNC(SYSDATE) + 1) + 6 / 24 WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM YOURTABLE

edited Aug 30 '21 at 20:50

BIBD

15,107
25
85
137

answered Oct 03 '18 at 08:42

Dominic Pajares

1

score -1 · Answer 4 · answered Apr 01 '20 at 13:36

Refresh the mv every day at 1 AM

CREATE MATERIALIZED VIEW test1 BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (trunc(sysdate)+1)+1/24 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE "Your query"

Create Materialized view which refresh records on daily

4 Answers4

Linked