Chris hasn't said why avoiding a replace of the MView was important,
but I'm guessing it was to avoid the full refresh.
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

-----Original Message-----
*From:* oracle-l-bounce_at_freelists.org [mailto:
[email protected]] *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?

On 11/28/06, Chris Marquez wrote:

Modify Snapshot without drop

RH Linux

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 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
the new column.

Adding the query via dbms_mview did not have any effect on
(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
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 4 of 13 | next ›
Discussion Overview
grouporacle-l @
postedNov 28, '06 at 5:05p
activeNov 30, '06 at 6:40p



site design / logo © 2023 Grokbase