This content has been marked as final. Show 6 replies
Chanchal Wankhade wrote:It will refresh on 15:28 hours, because it was created at 04:28 Hours (i.e. AM hours and in HH24 format) and you are adding 11 Hours to it, which will be PM hours then.
we have oracle 10g R2 on windows 64 Bit.
we have materialize view and its refresh time is as below
But i am not getting one point above. Will this mview would refresh on 11 am or 11 pm or or 4:28+11= 15:28 pm/am.
START WITH TO_DATE('16-Feb-2013 04:28:12','dd-mon-yyyy hh24:mi:ss') NEXT sysdate + 11/24
Chanchal Wankhade wrote:It will be 7 days. Because you are not dividing it by 24.
And what about sysdate+7.
It will refresh mview after 7 day's or 7 hrs.
SYSDATE + 7 = 7 days from SYSDATE
SYSDATE + 7/24 = 7 Hours from SYSDATE
SYSDATE + 7/24/60 = 7 Minutes from SYSDATE
SYSDATE + 7/24/60/60 = 7 Seconds from SYSDATE
So, MView will refresh every 7 days at the time it was created i.e. 04:28 hours.
I refer to a good article when I am in doubt of the Calendar syntax for schedulers Scheduler Calendar Examples.
Chanchal Wankhade wrote:Is your server location in India and has corresponding NLS_* settings? If yes, the below should work.
One last question
If i want to refresh it on 1 am (In the night India) and i am creating mview say 3 pm then what should be the START WITH parameter and NEXT parameter.
If not, then calculate the difference Server time with IST and adjust the Refresh Time (upto Minutes, if necessary).
SYSDATE + 10/24
A list of Different NLS settings is provided here. Have a look just in case you need to change them (obviously with impact analysis of the change). Locale Data