Need help with defining a materialized view refresh frequency 1

[rogers42]

Hi Folks,

I have a materialized view that is defined as follows.

create materialized view mv_test
BUILD IMMEDIATE
REFRESH COMPLETE
start with SYSDATE
next round(SYSDATE + 1) + 1/24
as
< my query >

Based on the above code, an somebody please confirm that the materialized view will be updated daily at 1 A.M?

Thanks in advance

rogers42

 

[karluk]

In all probability your approach will work, but it's safer to use "trunc" instead of "round" in order to avoid the possibility that the refresh job might not complete until after 12:00 noon - which would cause round(sysdate+1) to be two days from today instead of tomorrow.

create materialized view mv_test
BUILD IMMEDIATE
REFRESH COMPLETE
start with SYSDATE
next trunc(SYSDATE + 1) + 1/24
as
< my query >

 

[rogers42]

Thanks for the suggestion Karluk.

I would like the mv to be refreshed at 1 A.M. Hence "1/24" as opposed to "13/24". Is the syntax correct ?

Thanks in advance

rogers42

 

[rogers42]

I have re created the mv based on the recommendations. However, I get some puzzling results when I execute the following query

select what, last_date, next_date
from dba_jobs
where upper(what) like '%MV_TEST%';

Output:

dbms_refresh.refresh('"TEST"."MV_TEST"');
21-JAN-09

The "last_date" is missing. Most probably because I just created the mv 5 minutes ago and it has not refreshed as yet ???

But "next_date" should be 22-JAN-09.

Essentially, I would like my mv to refresh at 1 A.M daily. Where am I going wrong ?

Thanks

rogers42

 

[rogers42]

Please ignore my previous post. I re ran the query and it displayed the correct results

WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE NEXT_SEC
--------- --------- --------
dbms_refresh.refresh('"TEST"."MV_TEST"');
21-JAN-09 22-JAN-09 01:00:00

I think, it took a while for the update to kick in.

Thanks

rogers42