Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.