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
-- 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 :
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 |