[Oracle-l] MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?

On 11/28/06, Chris Marquez wrote:
Modify Snapshot without drop

RH Linux
9.2.0.5

I need to alter my base table (add columns) and thus also the remote db
MView referring to it.

I don't mind doing some RTFM and I have been on Metalink this morning
without much luck.

On 11/28/06, Freeman, Donald wrote:

I have one of these to do today. We are adding two columns on a table in
the source database. I have a materialized view of that table on my
warehouse DB. I was just planning on running create or replace mview as
select * from source; I do a full refresh anyway. Is that a problem for
you?

-----Original Message-----
*From:* oracle-l-bounce_at_freelists.org [mailto:
oracle-l-bounce@freelists.org] *On Behalf Of *Jared Still
*Sent:* Tuesday, November 28, 2006 2:52 PM
*To:* marquezemail_at_gmail.com
*Cc:* oracle-l
*Subject:* Re: MODIFY Materialized View Definition without dropping it (or
the MLog) after base table alter?

I just tried this on Oracle 10.2.0.2 on Linux:

Create table
Create MV log on table
Create Mv on source table ( all in same account on one database)

Add column to source table
Add same column to MV table
unregister and register snapshot with a new query via dbms_mview.register

A complete refresh still works for the original columns, but does not
update
the new column.

Adding the query via dbms_mview did not have any effect on
sys.snap$.query_txt
(as seen in dba_snapshots)

Updating sys.snap$ directly with a new query did not fix it either.

However, monkeying with snap$ did break the DD, as the following
error will show:

11:48:49 SQL> /
alter materialized view mv_target compile
*
ERROR at line 1:
ORA-12003: materialized view "MV_TARGET" does not exist

Oops. I guess that is why we don't modify the Data Dictionary. :)

There's probably other ways to go about this, but this is
the only one I tried.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On 11/28/06, Freeman, Donald wrote:
I was just planning on running create or replace mview as select * from
source;

I need to alter my base table (add columns) and thus also the remote db
MView referring to it.

But do the new columns need to be added to the remote MV also -
and if yes, will they have the same value - ie is it something
like

add (new_col default new_val) ?

On 11/28/06, Freeman, Donald wrote:
Yep, I believe you are right but I found a reference to that syntax used
by Arup Nanda on OTN . I'm going to skip ahead just drop the mv and
rebuild in about five minutes.

You can add the new column, but it will not be populated.

On 11/28/06, Alberto Dell'Era wrote:
I think there's a misunderstanding here, I was just asking to Chris
whether the new column, that has to be added on the base table,
possibly with a default value, has to be propagated to the MV also (so
with the same value) or not.

Eg
old mv : create materialized view as select a from t_at_dblink
say you "alter table t add (new_column int default 42)"
has the mv to be logically modified to
create materialized view as select a, new_column from t_at_dblink
or does it stay the same, ignoring new_column ?