Re: MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: marquezemail@xxxxxxxxx
- Date: Tue, 28 Nov 2006 11:51:57 -0800
On 11/28/06, Chris Marquez <marquezemail@xxxxxxxxx> 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
Other related posts:
