5

Say that you have two Oracle databases, DB_A and DB_B. There is a table named TAB1 in DB_A with a materialized view log, and a materialized view named SNAP_TAB1 in DB_B created with

CREATE SNAPSHOT SNAP_TAB1
REFRESH FAST
AS SELECT * FROM TAB1@DB_A;

Is there a way to query in DB_B the changes made to SNAP_TAB1 after each call to fast-refresh the materialized view ?

DBMS_SNAPSHOT.REFRESH( 'SNAP_TAB1', 'F' );

In DB_A, prior to the refresh, you can query the materialized view log table, MLOG$_TAB1, to see which rows have been changed in TAB1. I'm looking for a way to query in DB_B, after each refresh, which rows have been refreshed in SNAP_TAB1.

Thanks!

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
Aurelio Martin Massoni
  • 1,706
  • 3
  • 14
  • 19

1 Answers1

1

I think the lines below work with prebuilt table:

You can add a column in the table SNAP_TAB1.

For inserts You can put it on default sysdate => for every insert you'll have the timestamp of the insert.

For updates you can use a trigger. Because the column is not involved in the Materialized View, updating the column with the trigger won't be a problem.

Probaly better, with the trigger you can use an unique id to store in that column, incremented before every new refresh.(Obtaining the unique id may have different aproaches.)

Obviously, you can't track deletes with this idea.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76