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 <PWD>
USING 'ora10srv.domain.com';
Here is the script of the table getting data pulled from by the view's refresh.
CREATE TABLE POST_SCHEMA.T_BUILDINGS
(
BUILDING_KEY NUMBER(10) NOT NULL,
SEQUENCE NUMBER(5) NOT NULL,
CIVIC_NUMBER NUMBER(10) NOT NULL,
APPART MENT CHAR(5 CHAR),
STREET_SERIE NUMBER(10) NOT NULL,
STREET VARCHAR2(60 CHAR) NOT NULL,
POSTAL_CODE CHAR(7 CHAR),
STREET_CORNER CHAR(1 BYTE) NOT NULL,
STATUS CHAR(1 CHAR) NOT NULL,
NOTE VARCHAR2(100 BYTE),
SERIAL NUMBER(10) DEFAULT 0 NOT NULL,
IDE NUMBER(10) NOT NULL
)
TABLESPACE TBL_SPC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 504K
NEXT 7776K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
So, now that we have all the implicated tables and objects : What I've done as far as debugging is :
-
Raised the
UNDO_RETENTION
parameter way too high so that's out of way (20000) -
Made the undo tablespace very big.
-
Tried to compile the view by hand, it works. If I try to compile it and don't commit it though it will cause a similar error if I try to run it from a different session.
-
Got rid of
DBMS_REFRESH
and usedDBMS_MVIEW.REFRESH
instead withatomic_refresh
set tofalse
.
-Set the undo tablespace to GUARANTEE RETENTION
-Completely deleted and recreated all the indexes on the referenced distant database's table.
Still, I'm receiving the following error messages every nights :
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 11 with name "_SYSSMU11$" too small
ORA-02063: précédant line de ORA10P
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 2809
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 3025
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 2994
ORA-06512: à ligne 1
I will try openning an SR with oracle. Here is what you can spot on oracle support website regarding this specific issue :
Until a consistent, reproducible test case can be developed in-house this problem remains unresolved.
The bug has been closed at this point without finding a root issue. The problem is not easy to reproduce consistently.
Best Answer
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:
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+).
Why?
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):
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.