FAQ
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 have some old notes / docs on how to do this "unsupported" (8i days,
updating intern tables) and how to do this if the MView is ON PREBUILT, but
mine is not.

I'm looking for good references or HOWTO's if someone has some, or even save
me some time and let me know if this can be done at all�with supported SQL
syntax?

Sometimes proving something is not supported is harder than proving it is.

Hmm, the more I think about it, I'm also assuming that I can use the
existing MLog on the base table after I alter the base table�not sure this
is a correct assumption?

Thanks,

Search Discussions

  • Jared Still at Nov 28, 2006 at 7:51 pm

    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
  • Freeman, Donald at Nov 28, 2006 at 8:27 pm
    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
  • Jared Still at Nov 28, 2006 at 9:21 pm
    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
    you?

    -----Original Message-----
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@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
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 28, 2006 at 9:24 pm

    On 11/28/06, Freeman, Donald wrote:
    I was just planning on running create or replace mview as select * from
    source;
    create or replace materialized view?

    That works on view, but not on MV's.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Alberto Dell'Era at Nov 28, 2006 at 9:31 pm

    I need to alter my base table (add columns) and thus also the remote db
    MView referring to it.
    But do the new columns need to be added to the remote MV also -
    and if yes, will they have the same value - ie is it something
    like

    add (new_col default new_val) ?

    --
    Alberto Dell'Era
    "Per aspera ad astra"
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 28, 2006 at 10:26 pm

    On 11/28/06, Alberto Dell'Era wrote:
    I need to alter my base table (add columns) and thus also the remote db
    MView referring to it.
    But do the new columns need to be added to the remote MV also -
    and if yes, will they have the same value - ie is it something
    like

    add (new_col default new_val) ?
    You can add the new column, but it will not be populated.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Freeman, Donald at Nov 28, 2006 at 10:28 pm
    Yep, I believe you are right but I found a reference to that syntax
    used by Arup Nanda on OTN . I'm going to skip ahead just drop the mv
    and rebuild in about five minutes.

    -----Original Message-----
    From: Jared Still
    Sent: Tuesday, November 28, 2006 4:25 PM
    To: Freeman, Donald
    Cc: marquezemail_at_gmail.com; oracle-l
    Subject: Re: MODIFY Materialized View Definition without

    dropping it (or the MLog) after base table alter?



    On 11/28/06, Freeman, Donald wrote:


    I was just planning on running create or replace mview
    as select * from source;



    create or replace materialized view?


    That works on view, but not on MV's.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 28, 2006 at 10:38 pm

    On 11/28/06, Freeman, Donald wrote:
    Yep, I believe you are right but I found a reference to that syntax used
    by Arup Nanda on OTN . I'm going to skip ahead just drop the mv and
    rebuild in about five minutes.
    I found that article. That is example code(was not run), and I believe is a
    typo.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Alberto Dell'Era at Nov 28, 2006 at 11:27 pm

    But do the new columns need to be added to the remote MV also -
    and if yes, will they have the same value - ie is it something
    like

    add (new_col default new_val) ?

    You can add the new column, but it will not be populated.
    I think there's a misunderstanding here, I was just asking to Chris
    whether the new column, that has to be added on the base table,
    possibly with a default value, has to be propagated to the MV also (so
    with the same value) or not.

    Eg
    old mv : create materialized view as select a from t_at_dblink
    say you "alter table t add (new_column int default 42)"
    has the mv to be logically modified to
    create materialized view as select a, new_column from t_at_dblink
    or does it stay the same, ignoring new_column ?

    --
    Alberto Dell'Era
    "Per aspera ad astra"
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 29, 2006 at 1:33 am

    On 11/28/06, Alberto Dell'Era wrote:
    I think there's a misunderstanding here, I was just asking to Chris
    whether the new column, that has to be added on the base table,
    possibly with a default value, has to be propagated to the MV also (so
    with the same value) or not.

    Eg
    old mv : create materialized view as select a from t_at_dblink
    say you "alter table t add (new_column int default 42)"
    has the mv to be logically modified to
    create materialized view as select a, new_column from t_at_dblink
    or does it stay the same, ignoring new_column ?
    Ah, I see.

    If that were the case, there's little else to do.

    Adding a column to the table does not invalidate any of the
    objects used for the materialized view, and it will still work
    properly.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • MacGregor, Ian A. at Nov 29, 2006 at 2:04 am
    True, however the materialized view, I think, will be marked as invalid. Even though it will still work. I'm not sure if the invalidation happends only if the source table is dropped and recreated, or during 'alter table' operations as well. The invalid status can be cleared via

    alter compile;

    Ian

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Jared Still
    Sent: Tuesday, November 28, 2006 5:34 PM
    To: Alberto Dell'Era
    Cc: marquezemail_at_gmail.com; oracle-l
    Subject: Re: MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?

    On 11/28/06, Alberto Dell'Era wrote:

    I think there's a misunderstanding here, I was just asking to Chris
    whether the new column, that has to be added on the base table,
    possibly with a default value, has to be propagated to the MV also (so
    with the same value) or not.

    Eg
    old mv : create materialized view as select a from t_at_dblink
    say you "alter table t add (new_column int default 42)"
    has the mv to be logically modified to
    create materialized view as select a, new_column from t_at_dblink
    or does it stay the same, ignoring new_column ?

    Ah, I see.

    If that were the case, there's little else to do.

    Adding a column to the table does not invalidate any of the
    objects used for the materialized view, and it will still work
    properly.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Chris Marquez at Nov 30, 2006 at 6:40 pm
    All,

    Thanks all for the comments and ideas.

    It appears that this is still not simple in Oracle.

    I have a (long) plan to ALTER and RECREATE *things*.

    Jared was right...I need the new columns on both places and wanted to avoid
    the MV re-create and full refresh. That's because of the object type, size
    and dependencies;
    MVIEW

    CLOB

    CONTEXT INDEXES.

Related Discussions

Discussion Navigation
viewthread | post
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