CODEWITHSUNDEEP
oracle-databaseviewmaterialize
Angie
Angie

Reputation: 475

Materialized View Stopped Refreshing

A client has a materialized view with the following configuration and the refresh just stopped and we don't know why.

We have gathered information through the internet on reasons of why the view has stopped and still could not find the reason of this happening in order to continue with the refresh we recreated the view and restarted the database.

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;

Upvotes: 1

Views: 1890

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

Each periodic (= which has the start with and next clauses) mview refresh creates a job via dbms_job. Check the user_jobs dictionary view for the mview's refresh job...

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'
;

... and call the dbms_job.broken() stored procedure with broken => false argument if the above query result's broken column shows 'Y' or if the next_date column shows a value in a rather distant future (January 1, 4000 in my case).

Note: Don't forget to recompile the mview beforehand, if it needs compile.

Note: Don't forget that this might happen to your mview again in the future. The mview depends on remote objects, which may be unavailable occasionally, rendering the mview invalid and making its refresh job fail once. When the job fails 16 times, it (the job) gets "broken".

Upvotes: 4

Related Questions