Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

oracle

I have a master table, and several remote read-only materialized views on pre-built tables that are being fast refreshed to keep in sync with the master table, using a dblink.

The table definitions (master and remotes) are currently identical. I want to add a column to the master table, and eventually have any changes propagate to the remote tables…without breaking anything 🙂

I don't have much experience with this, so I'm not 100% sure how to proceed.

My current plan is:

  1. Add column to master table
  2. Add column to remote tables
  3. Drop the materialized views on the remote tables
  4. Recreate the materialized views on the remote tables, but with the added column.

What I want advice on is whether this should work, or do I need to do something with the materialized view log on the master as well?

Best Answer

As long as you don't add data between the last refresh before the column add and after the column add, you should be fine. If you must allow for this then the easiest thing would be to do a complete refresh at the end of your plan. You shouldn't need to re-create the materialized view log.

Setup

--Create master table.
drop table t1;
create table t1 (id number(10) primary key, datetime date);
insert into t1 values (1, sysdate-1);
create materialized view log on t1 with primary key;

--Create materialized view table.
drop materialized view t2;
drop table t2;
create table t2 as (select * from t1);

--Create materialized view.
create materialized view t2 on prebuilt table with reduced precision
refresh fast on demand
as select id, datetime from t1;

--Show an empty materialized view.
select * from t2;

--Add data to t1.
insert into t1 values (2, sysdate);
commit;

--Refresh
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t2;

Planned Column Add

--Add Column.
alter table t1 add n varchar2(1);
alter table t2 add n varchar2(1);

--Re-create the materialized view.
drop materialized view t2 preserve table;
--***T1 updates between last refresh and the following statement will be lost.
create materialized view t2 on prebuilt table with reduced precision 
   refresh fast on demand
   as select id, datetime, n from t1;

--Add data.
insert into t1 values (3,sysdate+1,'a');
commit;

--Refresh.
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t1;
select * from t2;
Related Question