Re: MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 28 Nov 2006 11:51:57 -0800
Message-ID: <bf46380611281151w528d9d66oca7d6a1cb93f7c98@mail.gmail.com>

On 11/28/06, Chris Marquez <marquezemail_at_gmail.com> 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.
>
>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 28 2006 - 13:51:57 CST