ORA-12034 Mview Younger Than Last Refresh in a Recently Created Mview (Doc ID 1276101.1)

Last updated on NOVEMBER 14, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Receiving ORA-12034 Mview younger than last refresh on fast refresh in a recently created Mview.
There is another mview created on the same table and it does not receive the error on refresh.
Have tried recreating both the mview and the log and still repeatedly have the same problem.

Need to create a materialized view on db SRIDW from a master table in the SRI database, the size of master table is 80GB, with fast replication method.

Tried running complete refresh. It also failed with ora-12034 error.
The structure of the table has not changed.
Created a dummy mview on the table and there is no problem with the refresh.

Both databases are running 10.2.0.4.

INSTANCE_NAME
----------------
SRI

SQL> select owner, name, snapshot_id from dba_registered_snapshots
where owner = 'RIG' and snapshot_site = 'SRIDW';

OWNER NAME SNAPSHOT_ID
---------- ------------------------------ -----------
RIG IVA_COMPRAS 1241 --> THE PROBLEMATIC MVIEW
RIG IVA_COMPRAS_DUMMY 1301 --> THE NEW DUMMY MVIEW

SQL> select log_owner, log_table, current_snapshots, snapshot_id from
dba_snapshot_logs where master = 'IVA_COMPRAS';

LOG_OWNER LOG_TABLE CURRENT_SNAPSHOTS
SNAPSHOT_ID
---------- ------------------------------ -------------------
-----------
RIG MLOG$_IVA_COMPRAS1 27-01-2010 13:29:34
234 --> IT IS USED BY OTHER MVIEW IN SCHEMA "CCONSOLIDADA"
RIG MLOG$_IVA_COMPRAS1 27-01-2010 13:47:25
1301 --> IT IS USED BY DUMMY MVIEW

SQL> select owner, name, snapshot_id from dba_registered_snapshots
where snapshot_id=234;

OWNER            NAME
SNAPSHOT_ID
---------- ------------------------------
-----------
CCONSOLIDADA CCC_MVW_G16_RANKING_CLIENTE
234 --> THIS IS THE OTHER REGISTERED MVIEW


The snapshot_id 1241 doesn't have a corresponding row in DBA_SNAPSHOT_LOGS for RIG.IVA_COMPRAS.

Tried manually registering the log. Tried dropping and recreating the mview log and mview.
The mview still does not show as registered. Even after recreating, the same error occurs on refresh.
Ran mview capabilities and it says that the log is newer than the mview.

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