Why Materialized views' status frequently go to NEEDS COMPILE
Posted on 2004-11-23
Hello Guru (Indian word equal teacher),
I am Vijay from Bangalore India, I am facing a frequent problem with my Materialized views in my Oracle9i DB. A summary of our setup:
I have two Oracle 9i database of same version, one as Production system and other as Distributor system. I have create around 40 Mat. Views on Distributor DB which are COMPLETE Refreshed once in a day (ITS ONEWAY DATA PROPOGATION from PROD DB TO DIS DB) through DB link.
Its often found that the M.views status turns to NEEDS COMPILE and the application or reports running on Distributor DB stops working.
To solve this problem, I have to drop & recreate M.views and releated synonyms,grants.... which is one of the tedious job.
Can you pls help me WHY my MVs goes to NEEDS COMPILE and HOW can i avoid this problem???
Eagerly waiting for early reply........