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


Search Discussions

Discussion Posts


Follow ups

Related Discussions

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



site design / logo © 2022 Grokbase