物化视图停止刷新

Question

客户端具有物化视图，其配置如下，刷新停止，我们不知道为什么。

我们通过互联网收集了有关视图停止的原因，仍然无法找到发生这种情况的原因，为了继续刷新我们重新创建视图并重新启动数据库。

CREATE MATERIALIZED VIEW QUMASIDOCVIEW 
TABLESPACE ISO_DATA 
PCTUSED 40 
PCTFREE 10 
INITRANS 2 
MAXTRANS 255 
STORAGE (
      INITIAL   64K 
      MINEXTENTS  1 
      MAXEXTENTS  UNLIMITED 
      PCTINCREASE  0 
      FREELISTS  1 
      FREELIST GROUPS 1 
      BUFFER_POOL  DEFAULT 
      ) 
NOCACHE 
LOGGING 
NOCOMPRESS 
BUILD IMMEDIATE 
REFRESH COMPLETE 
START WITH SYSDATE 
NEXT SYSDATE + 15/(60*24) 
WITH PRIMARY KEY 
AS 
/* Formatted on 10/30/2015 8:31:47 AM (QP5 v5.277) */ 
SELECT SUBSTR (UPPER (QDB.R_DOCNUMBER), 1, 250) AS COURSE_CODE, 
     REPLACE(SUBSTR (UPPER (QDB.R_DOCNUMBER), 1, 250) 
       || '(' 
       || QDB.R_VERSION 
       || ').pdf', '+','') 
     AS DOC_REF_CODE, 
    UPPER (REPLACE(QOEA.R_STRING_VALUE, '+','')) AS DOC_REF_REV, 
    REPLACE(QPL.R_PICKLISTVALUE, '+','') AS DOC_REF_LIB, 
    SUBSTR (QDB.TITLE, 1, 254) AS DESCRIPTION, 
    QDB.R_VERSION AS COURSE_REV, 
    DECODE (QDB.R_LIFECYCLESTATE, 
      1232, 'A', 
      1229, 'A', 
      1227, 'I', 
      'I') 
     AS COURSE_STATUS, 
    DECODE(QDB.R_LIFECYCLESTATE, 
      1232, TRUNC(QDB.R_EFFECTIVEDATE) + 15, 
      TRUNC(QDB.R_EFFECTIVEDATE)) AS EFFECTIVE_DATE, 
    DECODE (QDB.R_LIFECYCLESTATE, 
      1232, TRUNC(QDB.R_EFFECTIVEDATE), 
      TRUNC(QDB.R_CREATION_DATE)) AS ISSUE_DATE, 
    UPPER (QOEA.R_STRING_VALUE) AS SUBJECT_CODE, 
    QPL.R_PICKLISTVALUE AS DIVISION_CODE, 
    'DOC' AS TRAINING_TYPE, 
    'NA' AS GROUP_TYPE, 
    REPLACE(QFS.FILE_NAME, SUBSTR(QFS.FILE_NAME, INSTR(QFS.FILE_NAME, '.PDF'),100)) || '.PDF' AS FILE_NAME, 
    'Y' AS RETRAIN_REQ, 
    QDB.R_LIFECYCLESTATE, 
    QCR.OCL_NAME, 
    QFS.FILESTORAGECONTENT, 
    QCR.R_WORKFLOWPURPOSE AS COMMENTS 
    FROM [email protected] QDB, 
    [email protected] QEA, 
    [email protected] QOEA, 
    [email protected] QEA2, 
    [email protected] QOEA2, 
    [email protected] QPL, 
    [email protected] QF, 
    [email protected] QFS, 
    [email protected] QCR 
WHERE  QDB.R_LIFECYCLESTATE IN ('1232', '1229', '1227') 
    AND QDB.R_DOCTYPE_ID = QEA.R_TYPEID 
    AND QEA.R_ATTR_NAME = 'syn_doctype' 
    AND QEA.R_OBJECT_ID = QOEA.R_QM_EXT_ATTR_ID 
    AND QDB.R_OBJECT_ID = QOEA.R_PARENT_OBJECT_ID 
    AND QEA2.R_ATTR_NAME = 'syn_site' 
    AND QEA2.R_OBJECT_ID = QOEA2.R_QM_EXT_ATTR_ID 
    AND QDB.R_OBJECT_ID = QOEA2.R_PARENT_OBJECT_ID 
    AND QDB.R_DOCTYPE_ID = QEA2.R_TYPEID 
    AND QEA2.R_PICKLIST = QPL.LINK_FROM_ID 
    AND QOEA2.R_STRING_VALUE = QPL.R_PICKLISTNAME 
    AND QDB.R_OBJECT_ID = QF.PARENT_OBJECT_ID 
    AND QF.FULL_FORMAT = 'pdf' 
    AND QF.R_OBJECT_ID = QFS.R_OBJECT_ID 
    AND ((QDB.R_ISSYSTEM_COPY = 0 AND QDB.R_LIFECYCLESTATE IN ('1229', '1227')) OR (QDB.R_ISSYSTEM_COPY = 1 AND QDB.R_LIFECYCLESTATE = '1232')) 
    AND QDB.I_LATEST_FLAG = 1 
    AND QDB.R_VERSION LIKE '%.0' 
    AND QOEA.R_STRING_VALUE IN ('SOP', 
           'WI', 
           'Form', 
           'MAN', 
           'POL', 
           'QC', 
           'MBMR', 
           'RDG', 
           'QAR', 
           'MBPR', 
           'APR', 
           'SDS', 
           'VMP', 
           'SMF', 
           'CVP', 
           'GRAP', 
           'ASAR') 
    AND QDB.R_CHANGEREQUESTID = QCR.R_OBJECT_ID(+) 
    AND QDB.R_EFFECTIVEDATE >= SYSDATE - 9000; 

Answer 1

每个周期（=其具有start with和next子句）MVIEW刷新创建经由dbms_job的作业。检查user_jobs字典视图的MVIEW的刷新工作...

select job, last_date, next_date, broken, failures, what 
from user_jobs 
where lower(what) like '%dbms!_refresh.refresh%' escape '!' 
    and lower(what) like 'qumasidocview' 
; 

...，并呼吁与broken => false论点dbms_job.broken()存储过程，如果上面的查询结果的broken列显示'Y'或者如果next_date列显示的值一个相当遥远的未来（1月1日，我的情况是4000）。

注意：如果需要编译，请不要忘记预先重新编译mview。

注意：不要忘记，这可能会发生在你未来的mview。 mview依赖于远程对象，这些对象可能偶尔不可用，导致mview无效并使其刷新作业失败一次。当这项工作失败了16次时，它（工作）就会“破裂”。