使用MV總結

今天系統需要升級幾個MV，原本在測試庫上執行良好的指令碼但到正式環境還是有些出入的，

問題一：

首先是建立的時候，居然報：

ora-12060 shape of prebuilt table does not match definition query

原因是需要將select後的列名需要進行as 別名提示，暈死。。

問題二：

再就是執行好的指令碼居然上面都有小紅叉叉，叫人看了心裡好不爽，但是還是可以正常執行的，後來檢視metalink文章（264036.1）：

An mview becoming invalid does not mean it will not retain old data. You can still query the mview. Actually an mview with invalid status means it has old data and it is not in sync with the master table.

We were seeing the same behavior. in 9.2 and found Metalink note 264036.1 which says:

"This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview.

 

shit~~~~~
However, the query on MV will not return latest update done in master table unless MV is refreshed