-
Title
Materialized view is not replicated correctly. -
Description
When replicating materialized view to target table, only portion of data are replicated to target . For example, there are 81 rows deleted on source, but in target, there are only 70 rows deleted. Furthermore, no "out of sync" and no other errors are appearing in the log and compare/repair fix the problem with no issues.
-
Cause
N/A
-
Resolution
A workaround for the issue is to create the MV log with a time-based refresh. Following is an example of it.
-- Original table
CREATE TABLE
"HPDD_SIMLOCK"."SPPOC4"
("OWNER" VARCHAR2(30 byte) NOT NULL,
"OBJECT_NAME" VARCHAR2(30 byte) NOT NULL,
"SUBOBJECT_NAME" VARCHAR2(30 byte),
"OBJECT_ID" NUMBER NOT NULL, When replicated materialized view to target table, only portion of data are replicated to target . For example, there are 81 rows deleted on source, but in target, there are only 70 rows deleted. Furthermore, no "out of sync" and no other errors are appearing in the log and compare/repair fix the problem with no issues.
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19 byte),
"CREATED" DATE NOT NULL,
"LAST_DDL_TIME" DATE NOT NULL,
"TIMESTAMP" VARCHAR2(19 byte),
"STATUS" VARCHAR2(7 byte),
"TEMPORARY" VARCHAR2(1 byte),
"GENERATED" VARCHAR2(1 byte),
"SECONDARY" VARCHAR2(1 byte),
CONSTRAINT "SPPOC4_PK" PRIMARY KEY("OBJECT_ID")
USING INDEX
TABLESPACE "HPDD_INDEX04"
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "HPDD_TDE" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
MONITORING;
-- Create MV log
create materialized view log on HPDD_SIMLOCK.SPPOC4 tablespace mv_repl01;
--Create materialized view as a fallback solution
CREATE MATERIALIZED VIEW MV_SPPOC4
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST START WITH SYSDATE NEXT SYSDATE+1/1440
AS
select * from sppoc4;