FAQ
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
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
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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 13 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 28, '06 at 5:05p
activeNov 30, '06 at 6:40p
posts13
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase