Materialized view with REFRESH ON COMMIT is not working in ORACLE 19

64 Views Asked by At

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:

  1. Create a base table Test.
  2. Create a materialized view log MATVIEWLOG with RowID and including new values.
  3. Create a materialized view MATView with REFRESH FAST ON COMMIT.
  4. Insert data in the base table.
  5. 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;
0

There are 0 best solutions below