AnswerBun.com

Materialized view not purging (over dblink via view)

Database Administrators Asked by User1973 on December 23, 2020

I’ve created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c):

create materialized view primary_system.workorder_mv
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    [email protected] --view in external system that is served up by dblink (gistomax)
    --the view looks like this: select * from workorder where isgis=1


create materialized view log on external_system.workorder with rowid;  

The docs say materialized views are supposed to purge automatically:

Oracle automatically tracks which rows in a materialized view log have
been used during the refreshes of materialized views, and purges these
rows from the log so that the log does not grow endlessly. Because
multiple simple materialized views can use the same materialized view
log, rows already used to refresh one materialized view might still be
needed to refresh another materialized view. Oracle does not delete
rows from the log until all materialized views have used them.


Question:

So far, the MV log data doesn’t seem to be purging automatically:

enter image description here

Why aren’t’ the MV logs automatically purging once the records are synced?

One Answer

Try basing your MV directly on the source table and not on an intermediate view. Include your "isgis=1" condition in the MV query.

create materialized view primary_system.workorder_mv
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    [email protected]
    where isgis=1

Correct answer by pmdba on December 23, 2020

Add your own answers!

Related Questions

Install mysql through deb file on ubuntu 20.04

1  Asked on September 13, 2020 by ajeetkumar

       

Indexes not used for join

1  Asked on September 11, 2020 by dmitriy-grankin

     

tables inheritance stored procedure

1  Asked on September 10, 2020 by juclart-ngouedi

   

Schema for scarce goods

1  Asked on September 5, 2020 by empedokles

   

Restoring multiple files to multiple database with pg_restore

1  Asked on September 2, 2020 by irakli-ugulava

     

Getting midnight of today

4  Asked on August 25, 2020 by logu

   

Compare 2 columns in 2 different tables with non uniques values

3  Asked on August 18, 2020 by deepak-garg

 

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

10  Asked on August 14, 2020 by geoaxis

 

SSIS Package Not Being Deployed

1  Asked on August 14, 2020 by steveg

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP