Materialized Views (MVIEWs) Refresh Order Using "DBMS_SNAPSHOT.REFRESH" LIST Parameter; 9i Vs 10g and Higher Versions (Doc ID 1452382.1)

Last updated on MAY 10, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

In 10g and above versions, Oracle does not refresh the MVIEWs in sequence as specified in the "LIST" parameter in the DBMS_SNAPSHOT.REFRESH / DBMS_MVIEW.REFRESH procedure. MVIEWs are sorted and refreshed in alphabetical order from 10g onwards. Whereas in 9i, it refreshes the MVIEWs as specified in LIST parameter; there is no sorting by MVIEW name in 9i.

Changes

Customer used to refresh a set of MVIEWs based on its dependencies in 9i version.  After upgrading to 10g/11g, the same set of MVIEWs are getting refreshed in alphabetical order, and the impact is MVIEWs are not getting correct data as the order is changed.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms