Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    1

    Question Unanswered: Materialized Views problem

    Hello,

    I was wondering whether your able to help me solve a problem I'm experiencing with materialised views in Oracle. This is the first time I've encountered them, so you may need to help me out a bit with some of the concepts

    Basically I have two different materialized views (with corresponding logs), each of which is stored in seperate databases. The views are used to call each other to query data available from the other database

    Eg.


    DATABASE A_____________________________________ DATABASE B

    Materialized View A <--------------------------------> Materialized View B


    The underlying master tables that MV-B uses as part of its query in database B has been modified (such that there are different data types and column names in the master table). As a result when MV-A attempts to query MV-B, an error occurs.

    I've tried to Googling a way of either updating the SELECT query within MV-B so that it corresponds with the new structure of its underlying master table, but haven't found anything. There has been a few websites talking about conducting a complete "refresh" of MV-B, but I'm not sure whether that would just re-execute the original query to gather updated data - which shouldn't work as the SELECT query is incorrect.

    Any advice on how to update the SELECT query in a materialized view would be much appreciated, and whether there are other dependencies I need to be aware of if such an update did occur.

    Thanks,

    Rick

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If the base query is no longer valid, you must drop the MVIEW and regenerate it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •