This discussion is archived
8 Replies Latest reply: Sep 9, 2012 12:10 PM by Mark Malakanov (user11181920) RSS

updatable materialized views

Exor Newbie
Currently Being Moderated
Hello,

I have 6 updatable materialized views that did not refresh in 6 months. Even though the table has 12k records the mlog has 1.000.000 entries. needless to say when i try to fast refresh it hangs forever. What can I do to refhresh and not loose the changes on either side?
Would a complete refresh of updatable mview will perform both way replication? What if since the last refresh the same field was updated in both envs? Which one will prevail?

Thanks,
  • 1. Re: updatable materialized views
    damorgan Oracle ACE Director
    Currently Being Moderated
    Dup removed.

    Edited by: damorgan on Sep 6, 2012 1:35 PM
  • 2. Re: updatable materialized views
    damorgan Oracle ACE Director
    Currently Being Moderated
    This may, unfortunately, be a duplicate response as the forum software is behaving strangely ... if so I apologize in advance.

    Did your Oracle installation come with a version number?

    Did your materialized views come with DDL?

    Do you think we can advise you without a clue as to what version and the MV type?
  • 3. Re: updatable materialized views
    Exor Newbie
    Currently Being Moderated
    Hello Damorgan,

    Thank you for your reply and sorry for missing the to include the relevant information:

    Oracle version is 10.2.0.4.0.

    MVs look like this:


    CREATE MATERIALIZED VIEW schema.mview_name
    NOCACHE
    LOGGING
    NOCOMPRESS
    NOPARALLEL
    BUILD IMMEDIATE
    REFRESH FAST
    START WITH TO_DATE('30-May-2012 20:12:39','dd-mon-yyyy hh24:mi:ss')
    WITH PRIMARY KEY
    FOR UPDATE
    AS
    /* Formatted on 06/09/2012 4:53:08 PM (QP5 v5.185.11230.41888) */
    SELECT *
    FROM schema.table@link c;

    Thanks again,
  • 4. Re: updatable materialized views
    damorgan Oracle ACE Director
    Currently Being Moderated
    Based on what you have posted my instinct would be to ask three questions.

    1. Why do you have desupport mode software that has not even been patched to its own terminal release?
    2. Why does anyone care if after all this time no one noticed that the MV was hopelessly out of date?
    3. What else is going on that makes anyone care how long it takes for this to finally refresh?

    If it were my MVs and my database first off it would be 11.2.0.3. But more importantly ... I'd rename everything that currently exists ... create new MVs ... and load them as though this was a new requirement.
  • 5. Re: updatable materialized views
    Exor Newbie
    Currently Being Moderated
    1. Why do you have desupport mode software that has not even been patched to its own terminal release?
    Because of lack of $$$
    2. Why does anyone care if after all this time no one noticed that the MV was hopelessly out of date?
    Up to now there was someone that would run a script that would check differences and execute updates, some kind of convoluted manual replication solution, but he resigned recently. We are now trying to clean up and set everything up like it was designed.
    3. What else is going on that makes anyone care how long it takes for this to finally refresh?
    Online processing?


    But more importantly ... I'd rename everything that currently exists ... create new MVs ... and load them as though this was a new requirement.
    Well, we can't loose the data that is already there...So:
    1. rename master
    2. create new master; insert into new master the data from old master;
    3. rename mview
    4. create new mview; at this point new mview has the data of old master; how do I reconcile with the data from the old mview?

    tks again,
  • 6. Re: updatable materialized views
    Exor Newbie
    Currently Being Moderated
    Also can you please let me know what happens during complete refresh with these updatable mviews? Is the refresh complete both ways? Or is the mview re-initialized with the master data only?
  • 7. Re: updatable materialized views
    damorgan Oracle ACE Director
    Currently Being Moderated
    1. If the organization can not afford a support agreement ... consider updating your resume.
    2. Based on this answer alone I'd blow away the existing MVs and rebuild correctly if you possibly can.
    3. Look at the syntax for creating a materialized view from a prebuilt table. I have a demo on my website and Oracle's docs are available online to guide you.
  • 8. Re: updatable materialized views
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Would a complete refresh of updatable mview will perform both way replication? What if since the last refresh the same field was updated in both envs? Which one will prevail?
    if you look at Concepts http://docs.oracle.com/cd/B19306_01/server.102/b14226/repmview.htm
    An updatable materialized view log (USLOG$_materialized_view_name) is used to determine which rows must be overwritten or removed from a materialized view during a fast refresh. A read-only materialized view does not create this log, and Oracle does not use this log during a complete refresh because, in this case, the entire materialized view is replaced.
    I would experiment first with creating a similar test UMV, done updates on both sides and do complete refresh.
    2. Why does anyone care if after all this time no one noticed that the MV was hopelessly out of date?
    Up to now there was someone that would run a script that would check differences and execute updates, some kind of convoluted manual replication solution,
    It is not clear to me what is a purpose of "a script that would check differences and execute updates, some kind of convoluted manual replication solution" while you are already using the updateable fast MV, which is itself kind of "difference based update"?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points