I am trying to create materialized views and materialized view log but the materialized views are not getting refreshed even after the use of REFRESH ON COMMIT in ORACLE 19. The same code for the materialized view and view log is working fine with the ORACLE 12.
I have followed below steps:
- Create a base table Test.
- Create a materialized view log MATVIEWLOG with RowID and including new values.
- Create a materialized view MATView with REFRESH FAST ON COMMIT.
- Insert data in the base table.
- The data is not getting updated in the MATView(Materialized view).
Note: The above steps are working fine in the ORACLE 12.
Problem: The updated data is not coming in the Materialized view of ORACLE 19?
I have tried by using primary key/rowId in the materialized view log but same behaviour has been found.
below is the sample code for the issue: Creation Of Materialized view log
CREATE MATERIALIZED VIEW LOG ON "TEST"."MATVIEWLOG"
TABLESPACE "TEST123"
WITH ROWID, SEQUENCE ( "NAME", "TYPE", "POINTS", "ANUMBER", "SOURCETYPE", "TARGETNUMBER", "CODE", "CODETYPE", "VARIANCE" ) INCLUDING NEW VALUES;
Creation of Materialized view
CREATE MATERIALIZED VIEW "TEST"."MATView" ("CODE", "CODETYPE", "SOURCETYPE", "FREQUENCY")
SEGMENT CREATION IMMEDIATE
NOCOMPRESS LOGGING
TABLESPACE "TEST123"
BUILD IMMEDIATE
TABLESPACE "TEST123"
REFRESH FAST ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
AS select Code, CodeType, SourceType, COUNT(*) AS Frequency from TestTable group by Code, CodeType, SourceType;