物化视图停止刷新

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 qdev.QM_DOCUMENT_BASE@ISOQUMAS QDB,
   qdev.QM_EXT_ATTR@ISOQUMAS QEA,
   qdev.QM_OBJECT_EXT_ATTR@ISOQUMAS QOEA,
   qdev.QM_EXT_ATTR@ISOQUMAS QEA2,
   qdev.QM_OBJECT_EXT_ATTR@ISOQUMAS QOEA2,
   qdev.QM_PICKLIST_R@ISOQUMAS QPL,
   qdev.QM_FILESTORAGE@ISOQUMAS QF,
   qdev.QM_FILESTORAGECONTENT@ISOQUMAS QFS,
   qdev.QM_CHANGEREQUEST_BASE@ISOQUMAS 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'
;

..。如果上述查询结果的dbms_job.broken()列显示'Y'，或者如果next_date列在相当遥远的将来显示值，则调用带有broken => false参数的broken存储过程(在我的例子中是1月1日，4000 )。

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

备注：不要忘记，这可能会再次发生在您的mview在未来。mview依赖于远程对象(这些对象可能偶尔不可用)，使mview无效并使其刷新作业失败一次。当任务失败16次时，它(任务)就会被“破坏”。