Re: Materialized View: New column in master table is not replicated

Hans-Peter Reu wrote:
> Hello NG!
>
> I created this Materialized View to replicate a table to another database:
> CREATE MATERIALIZED VIEW MyTblName TABLESPACE MyTblSpace REFRESH FAST AS
> SELECT * FROM MyTblName_at_l_source;
> Anything works fine!
>
> Now I added a new column "Field4" to the master table in the source
> database.
> I expected that the new column is replicated automatically because I used
> "SELECT * FROM MyTblName_at_l_source"
> But the new column is not replicated to the destination database.
> I think the reason is that Oracle (10g) ported the "SELECT * FROM
> MyTblName_at_l_source" to
> SELECT "MyTblName"."Field1" "Field1","MyTblName"."Field2"
> "Field2","MyTblName"."Field3" "Field3" FROM "MyTblName"@l_source "MyTblName"
>
>
> First question: Is it possible to create a Materialized View that
> automatically
> replicates a master table with all columns - new columns too?
>
> Second question: I searched for a command something like "ALTER MATERIALIZED
> VIEW"
> to tell oracle to replicate the new column too. But I did not found it.
> Do I simply have to create the Materialized View once more?
>
>
> Thanks for your help!
> I appreciate it!
> I searched a lot of documentations/books but did not found an answer!
>

Q1: Yes, that's more or less what it does, and that's why your new column is in the master table but not in the snapshot (MV).

Q2: By far the simplest and most reliable way is simply to drop and recreate the MV, if you have the time.

Palooka Received on Thu Nov 13 2008 - 14:08:30 CST