0

Hi I have created 5 MViews for tables in Remote DB 2 days back in production environment. Out of that one Mview, at the time of creation it worked fine. But 2 days after(today) the MView's last refresh date is still showing the day before yesterday's date.

  1. I checked on the DBA_JOBS: LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,FAILURES,WHAT NULL,"23-JUL-13 01:00:00", N,"trunc(SYSDATE+1)+1/24",12,"dbms_refresh.refresh('"OCCSS_ENTMT_HK"."MV_SCI_STD_CODE_VALUE"');"

MView create statement:

CREATE MATERIALIZED VIEW MV_SCI_STD_CODE_VALUE   BUILD IMMEDIATE   USING INDEX   REFRESH FORCE ON DEMAND START WITH SYSDATE+0 NEXT TRUNC(SYSDATE+1)+1/24   WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT   USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE   AS SELECT STV_STD_CODE_NUM STANDARD_CODE,
    STV_STD_CODE_VALUE STANDARD_CODE_VALUE,
    STV_STD_CODE_VALUE_DESC DESCRIPTION,
    stv_prnt_std_code_value parent_code   FROM SCIADMIN.P03_STD_CODE_VALUE@SCI_LINK02.HK.BT.COM P03   WHERE STV_STD_CODE_NUM    IN ('19','31','54','5','6','300')   AND p03.update_status_ind <> 'D';

   COMMENT ON MATERIALIZED VIEW MV_SCI_STD_CODE_VALUE  IS 'snapshot table for snapshot MV_SCI_STD_CODE_VALUE';

The Mview is scheduled to run once in one day. But the failures shown is 12.

  1. I couldn't find any jobs for the above in DBMS_JOBS_RUNNING.

I need to investigate this issue. What would be the possible issues which could have caused this failure?

explorer da
  • 97
  • 1
  • 7
  • Check the alert log for the error message. – Jon Heller Jul 22 '13 at 05:06
  • Hi jonearles, Thanks. We have requested DBAs to send us the logs. Do you suspect whether "WITH PRIMARY KEY" clause would have caused the issue? Will there be a duplicate value in the remote table? But i donot have the remote table's data in hand now. – explorer da Jul 24 '13 at 23:26
  • That's possible, but it could be a lot of things. Once you get the ORA- number it may be easy to solve. – Jon Heller Jul 24 '13 at 23:34

0 Answers0