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

Last updated on NOVEMBER 04, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
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 :


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



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

create user mvlog identified by mvlog;
alter user mvlog default tablespace users;
grant dba to mvlog;

connect mvlog/mvlog@orc1_localhost;
drop table store;

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

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

insert into store
values(2,102);

commit;

drop materialized view log on store ;

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


desc store

select * from store;


drop table transactions;

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

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

commit;

drop materialized view log on transactions;

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

select * from transactions;

drop materialized view sum_store_trans;

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

update transactions
set purchase_amt=300
where trans_id=1;

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

commit;


select * from sum_store_trans;


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

commit;

select * from sum_store_trans;

--no problems with above statements

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

select * from transactions

update transactions
set purchase_amt=300
where trans_id=1;

commit;

select * from transactions


select * from sum_store_trans;
-- 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.

 

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