1 Reply Latest reply: Sep 8, 2013 7:10 AM by remcobrand RSS

    how to get a materialized view get to refresh itself after the job is "broken"

    remcobrand

      we created a materialized view sometime ago with the following statement:

       

      create materialized view SXV_PUB_EMPLOYEE_CERT_ALL_M

      refresh complete on demand

      start with to_date('30-08-2009 04:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate) + (28/24)

      as

      select  sxv_emp_cert_all.*

      from    sxv_employee_certification_all sxv_emp_cert_all;

       

      this week we found out it had not been refresh for about a month

      In dba_jobs the column broken was 'Y', next_date time something like 01-01-4000 and failures 16

       

      when I ran it manually by executing

      BEGIN DBMS_MVIEW.REFRESH('SXV_PUB_EMPLOYEE_CERT_ALL_M', 'C'); END;

       

      I found that one of the columns was too small (probably a columns of one of the underlying tables had been extended since the creation of the materialized view)

      After fixing this I ussied yesterday (on 29-8-2013) the statement :

      alter materialized view SXV_PUB_EMPLOYEE_CERT_ALL_M

      refresh complete on demand

      start with to_date('30-08-2009 04:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate) + (28/24)

      after this the table dba_jobs showed me 30-08-2013 04:00:00 as next date

      I was expecting it to run this night at 04:00, but it didn't

      the last_date column value was still from about a month ago, the column broken still shows 'Y'

      and the next date 30-08-2013 04:00:00 (while it should been set to 31-08-2013 01:00:00

      Rrunning

      BEGIN DBMS_MVIEW.REFRESH('SXV_PUB_EMPLOYEE_CERT_ALL_M', 'C'); END;

      gave no errors this time

      and in User_Mview_Analysis the last_refresh_date column showed the date/time I had executed it

      Any idea how to get the job "unbroken" again so that the view refreshes itself every night?

       

      the database is Oracle Database 10g Release 10.2.0.4.0

       

      regards,

       

      Remco