Thread: Materialized Views problem
08-20-08, 07:23 #1Registered User
- Join Date
- Aug 2008
Unanswered: Materialized Views problem
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
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.
08-20-08, 10:12 #2Registered User
- Join Date
- Jun 2004
- Liverpool, NY USA
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.