6 Replies Latest reply: Feb 25, 2013 11:53 PM by Purvesh K RSS

    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.
              • 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