My Oracle Support Banner

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

Last updated on OCTOBER 04, 2019

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
Oracle Database Exadata 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 :
------------------------------------------------

create user TEST_USER identified by <pwd>;
alter user TEST_USER default tablespace users;
grant dba to TEST_USER;

connect TEST_USER/<pwd>@<dblink_name>;
drop table TEST1;

create table TEST1(store_id int,store_num numeric(3,0));
alter table TEST1 add constraint store_pk primary key(store_id,store_num);

insert into TEST1
values(1,101);
commit;

insert into TEST1
values(2,102);

commit;

drop materialized view log on TEST1 ;

CREATE MATERIALIZED VIEW LOG ON TEST1
WITH rowid(store_id,store_num)
including new values;


desc TEST1

select * from TEST1;


drop table TEST2;

create table TEST2(trans_id int,store_id int,purchase_date date,purchase_amt numeric(21,4));
alter table TEST2 add constraint TEST2_pk primary key (trans_id);

insert into TEST2
values(1,1,to_date('01/31/2005','mm/dd/yyyy'),100.);

commit;

drop materialized view log on TEST2;

CREATE MATERIALIZED VIEW LOG ON TEST2
WITH rowid(trans_id,store_id,purchase_amt)
including new values;

select * from TEST2;

drop materialized view TEST_MV;

create materialized view TEST_MV
PARALLEL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
select s.store_num,sum(purchase_amt) as total_sales
from TEST2 t,TEST1 s
where s.store_id=t.store_id
group by s.store_num;

update TEST2
set purchase_amt=300
where trans_id=1;

insert into TEST2
values(2,1,to_date('12/31/2005','mm/dd/yyyy'),1200.);

commit;


select * from TEST_MV;


insert into TEST2
values(3,2,to_date('12/31/2005','mm/dd/yyyy'),2000.);

commit;

select * from TEST_MV;

--no problems with above statements

--update transaction value and confirm that it's updated in view

select * from TEST2

update TEST2
set purchase_amt=300
where trans_id=1;

commit;

select * from TEST2


select * from TEST_MV;
-- the above statement will show that the last update statement didn't captured by the refresh on the last commit.
-- Now take a look on the alert log and will find the above errors.

 

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.