Snapshot too old from materialized view refresh job

This Oracle database has a DBMS_JOB that runs the following materialized view refresh script :

dbms_refresh.refresh(ABCD.V_POSTAL_CODES);

If I run it by hand, it's finebut if the job executes it a snapshot too old occurs. It will also around 1/5 of the time simply succeed for no apparent reasons.

Here is the error in question (Database is installed in french):

ORA-12012: erreur d'exécution automatique du travail 16220
ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too small
ORA-02063: précédant line de ORA10SRV
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 2809
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 3025
ORA-06512: à "SYS.DBMS_IREFRESH", ligne 689
ORA-06512: à "SYS.DBMS_REFRESH", ligne 195
ORA-06512: à ligne 1

This is the script of the materialized view in question, it calls a DB_LINK to an oracle 10g database to retrieve the view's data :

CREATE MATERIALIZED VIEW ABCD.V_POSTAL_CODES (CODE_POST)
TABLESPACE TBL_SPC
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          200K
            NEXT             216K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('09-12-2016 00:01:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE) + 1.0007    
WITH PRIMARY KEY
AS 
/* Formatted on 2016-12-08 08:39:50 (QP5 v5.256.13226.35510) */
SELECT DISTINCT postal_code code_post
  FROM t_buildings@ora10srv
 WHERE postal_code IS NOT NULL;

You can see in the second last line of the script above, the name of the dblink. Here's the script of the dblink :

```
CREATE DATABASE LINK ORA10SRV
CONNECT TO POST_SCHEMA
IDENTIFIED BY

Hints

Just a quick summary of possible ways to provide you with additional information.

Job History

You might get additional information if you query the job history:

SELECT  /*+ RULE */
    A.JOB JOB#, 
    SCHEMA_USER MVIEW_OWNER,
    substr(DECODE(SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2),NULL,SUBSTR(WHAT,1,80), 
    SUBSTR(WHAT,INSTR(WHAT,'.',1,2)+2,INSTR(WHAT,'”',1,4)-4-INSTR(WHAT,'.',1,2)+2)),0,80) JOB_DESCRIPTION,
    to_char(LAST_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DATE, 
    to_char(NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_SCHED_RUN_DATE, 
    DECODE(BROKEN,'Y','YES','N','NO',' ') IS_BROKEN,
    FAILURES,  
    RUNNING IS_RUNNING, 
    B.SID SID 
FROM DBA_JOBS A LEFT OUTER JOIN 
    (SELECT /*+ RULE */ JOB,'YES' RUNNING,SID FROM DBA_JOBS_RUNNING ) B 
ON A.JOB = B.JOB 
ORDER BY SCHEMA_USER, JOB_DESCRIPTION;

Reference: My refresh materialized view is not refreshing – Part 1

Depending on what results you get, you might be able to look for additional information.

Rollback vs. Undo

Is your database running with Rollback Undo Segments? You might want to consider switching to the Undo Segments instead (10g+).


To enable SMU, set the UNDO_MANAGEMENT parameter to TRUE

Why?

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.

Reference: What is the difference between Rollback and Undo Tablespace?, section Undo vs. Rollback

Advisor Framework

If you are running in Undo Segements mode then you can retrieve advisory information about the Undo configuration from the system with the following script (Advisor Framework):

set serveroutput on

 DECLARE
  v VARCHAR2(300); 
 BEGIN
   v := dbms_undo_adv.undo_advisor(SYSDATE-1/1440, SYSDATE, 1);
   dbms_output.put_line(v);
 END;
 /

Reference: Oracle DBMS_UNDO_ADV

If you find your database doesn't have sufficient Undo Segments, then you might have to resize your UNDO tablespace to accommodate for the large amount of modifications/moves your MV is performing.

StackExchange Database Administrators Q#157577, answer score: 2