FAQ
I have a read only materialized view on one server and the master table
on another server. I need to add a column to the master table and the
materialized view is NOT defined with=20
< create materialized view ... as select * from table_at_mastersite >

The MV specifies the columns to replicate and I'm not interested in
replicating the new column. Do I still need to perform the DDL via the
dbms_repcat.alter_master_repobject(...) package procedure? Will
replication break if I alter the master table without this? (9.2/RHEL3)

TIA,

Steve Orr
Bozeman, Montana

Search Discussions

  • Hemant K Chitale at Jun 14, 2005 at 11:00 am
    I would say that you would use DBMS_REPCAT.ALTER_MASTER_REPOBJECT
    for Advanced Replication -- ie Multi Master Replication.

    What you have is a (Read Only) Materialized View, aka Snapshot in earlier
    versions.

    You can most certainly add a new column to the source table,
    with a mere ALTER TABLE .. ADD COLUMN ..

    On the other hand, if you wanted the new column to be seen in the
    MV as well ....
    Even if you had created it with an .. AS SELECT * ..., that would not
    automatically re-read the new column as the "local" definition would
    have been instantiated when you created the Materialized View.
    In my opinion, to add a column, you'd have to DROP and CREATE the MV.
    {but I hope, for your sake, to be proven wrong}.
    Hemant
    At 10:47 PM Tuesday, Orr, Steve wrote:
    I have a read only materialized view on one server and the master table
    on another server. I need to add a column to the master table and the
    materialized view is NOT defined with=20
    < create materialized view ... as select * from table_at_mastersite >

    The MV specifies the columns to replicate and I'm not interested in
    replicating the new column. Do I still need to perform the DDL via the
    dbms_repcat.alter_master_repobject(...) package procedure? Will
    replication break if I alter the master table without this? (9.2/RHEL3)
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
  • Jared Still at Jun 14, 2005 at 11:06 am

    On 6/14/05, Hemant K Chitale wrote:


    Even if you had created it with an .. AS SELECT * ..., that would not
    automatically re-read the new column as the "local" definition would
    have been instantiated when you created the Materialized View.
    In my opinion, to add a column, you'd have to DROP and CREATE the MV.
    {but I hope, for your sake, to be proven wrong}.
    Hemant
    I *believe* but have not verified that if you created the MV with a prebuilt
    table, it would a matter of adding the column to the table and doing a
    complete refresh.

    It might be necessary to drop/create the MV in this case, but it is a fast
    operation, and doesn't make it necessary to regrant permissions.

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

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 14, '05 at 10:52a
activeJun 14, '05 at 11:06a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase