Skip to Main Content
  • Questions
  • MView fast refresh - snapshot to old

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Dario.

Asked: June 06, 2017 - 5:57 pm UTC

Last updated: June 12, 2017 - 12:45 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,

When executing fast refresh over DbLink on MView "snapshot to old" error is returned.

Refresh is executed at database DB_MV:

dbms_mview.refresh( list => 'MV_TABLE_NAME');


Master table is located on DB_MASTER

At execution time:
- only 10% of UNDO is used at DB_MV database
- undo_retention=7200 (2 hours) ad DB_MV database
- error is returned after 47minutes

Is it possible that snapshot to old occurres on DB_MASTER database?
Do you have any idea where to check why this error occurres?

Full error returned is:
------------------------
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 71 with name "_SYSSMU71_2731661872$" too small
ORA-02063: preceding line from MOBCST1_MVIEWS
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

Br,
Dario

and Connor said...

"Is it possible that snapshot to old occurres on DB_MASTER database?"

Definitely. You can see the error stack:

"ORA-02063: preceding line from MOBCST1_MVIEWS", which is the database link where the select statement ran from.

Check the undo settings on the *source* database, and look at V$UNDOSTAT to get some metrics on the maximum duration of queries (including your refresh) and choose an appropriate larger size.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dario Nikoli%C4%87, June 12, 2017 - 12:58 pm UTC

Tnx

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here