My Oracle Support Banner

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

Last updated on FEBRUARY 20, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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 from the remote master table 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.


SQL> select owner, name, snapshot_id from dba_registered_snapshots where owner = '<OWNER_NAME>' and snapshot_site = '<SITE_NAME>';

OWNER NAME SNAPSHOT_ID
---------- ------------------------------ -----------
<OWNER_NAME> <MVIEW_NAME> 1241 --> THE PROBLEMATIC MVIEW
<OWNER_NAME> <MVIEW_NAME_DUMMY> 1301 --> THE NEW DUMMY MVIEW

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

LOG_OWNER LOG_TABLE CURRENT_SNAPSHOTS
SNAPSHOT_ID
---------- ------------------------------ ------------------- -----------
<OWNER_NAME> MLOG$_<MASTER_TABLE> 27-01-2010 13:29:34 234 --> IT IS USED BY OTHER MVIEW IN ANOTHER SCHEMA
<OWNER_NAME> MLOG$_<MASTER_TABLE> 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
----------   ------------------------------  -----------
<OWNER_NAME> <MVIEW_NAME>                    234 --> THIS IS THE OTHER REGISTERED MVIEW


The snapshot_id 1241 doesn't have a corresponding row in DBA_SNAPSHOT_LOGS 

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.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.