6 Replies Latest reply on Feb 26, 2013 5:53 AM by Purvesh K

    Mviews Refresh time

    Chanchal Wankhade
      Dear All,

      we have oracle 10g R2 on windows 64 Bit.

      we have materialize view and its refresh time is as below
      START WITH TO_DATE('16-Feb-2013 04:28:12','dd-mon-yyyy hh24:mi:ss')
           NEXT sysdate + 11/24      
      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.

      Please suggest.
        • 1. Re: Mviews Refresh time
          Purvesh K
          Chanchal Wankhade wrote:
          Dear All,

          we have oracle 10g R2 on windows 64 Bit.

          we have materialize view and its refresh time is as below
          START WITH TO_DATE('16-Feb-2013 04:28:12','dd-mon-yyyy hh24:mi:ss')
               NEXT sysdate + 11/24      
          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.

          Please suggest.
          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.
          • 2. Re: Mviews Refresh time
            Chanchal Wankhade
            Hi,

            And what about sysdate+7.

            It will refresh mview after 7 day's or 7 hrs.
            • 3. Re: Mviews Refresh time
              Purvesh K
              Chanchal Wankhade wrote:
              Hi,

              And what about sysdate+7.

              It will refresh mview after 7 day's or 7 hrs.
              It will be 7 days. Because you are not dividing it by 24.

              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.
              1 person found this helpful
              • 4. Re: Mviews Refresh time
                TSharma-Oracle
                After 7 days
                • 5. Re: Mviews Refresh time
                  Chanchal Wankhade
                  Purvesh,

                  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.


                  Thanks.
                  • 6. Re: Mviews Refresh time
                    Purvesh K
                    Chanchal Wankhade wrote:
                    Purvesh,

                    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.


                    Thanks.
                    Is your server location in India and has corresponding NLS_* settings? If yes, the below should work.
                    SYSDATE + 10/24
                    If not, then calculate the difference Server time with IST and adjust the Refresh Time (upto Minutes, if necessary).

                    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
                    1 person found this helpful