My Oracle Support Banner

Materialized View Not Reflecting Updates Or Deletes (Doc ID 434237.1)

Last updated on FEBRUARY 21, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

-- Problem Statement:
When create local materialized views using REFRESH FAST on COMMIT
some times it doesn't accept the updates and deletes . Only the inserted records is being refreshed fine.
Even if included "including new values" function in the materialized view log for each master table.

Although the materialized view correctly updates the rows when new records are inserted, the view
does not update itself correctly when rows from the master tables are either deleted or updated.

No error messages returned to the user interface. But in the alert log we find some errors like the next :

Wed May 30 17:58:00 2007
Following on-commit snapshots not refreshed :
TEST_USER.TEST_MV
Wed May 30 18:11:01 2007
Following on-commit snapshots not refreshed :
TEST_USER.TEST_MV
Wed May 30 18:12:43 2007
Following on-commit snapshots not refreshed :
TEST_USER.TEST_MV
Wed May 30 18:14:44 2007
Following on-commit snapshots not refreshed :
TEST_USER.TEST_MV
Wed May 30 18:39:10 2007
Following on-commit snapshots not refreshed :
TEST_USER.TEST_MV



The next is simple test case explain the issue :
------------------------------------------------

 

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

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