Why Materialized views' status frequently go to NEEDS COMPILE

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........

urs friendly
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ILCLAuthor Commented:
I forgot to metion that, there were no DDL done on my PRoduction DB tables. That is, no modification done on base table of MVs.
Subsequnetly to confirm this, I have also a put a Database level trigger on all DDL statement which mails me on all Alter/Create or DROP of any objects.

So pls help me in finding a solution to this never ending problem

Hi Vijay,

It is difficult to tell why your MVs are getting invalid without looking at the setup on your master and snapshot sites. Can you take one table for example and show the scripts you ran to set up the materialized view logs and groups on both the PROD and DIS databases for that table. Do you by any chance have master groups setup on the snapshot site ? I have seen this to make the MV invalid whenever there is a DML done on the master table.

In any case when a MV becomes invalid, you dont have to drop and recreate it, you can just recompile it. A script like this can generate the lines required to recompile all the invalid MVs in your schema.
SQL> select 'alter materialized view ' || mview_name || ' compile' from user_mviews where compile_state<>'VALID';

Until the problem is diagnosed and fixed, you could keep recompiling the MVs maybe by using a scheduled job.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ILCLAuthor Commented:
Hello Mr.Dutta,
Pls find the MV created script with table structure.

START WITH to_date('25-Nov-2004 01:34:46 PM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 1

--this SVRLK is a DB link to my PRD DB and the scrip is run on my DIS DB
--table structure
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 TRH_ORM_CD                                NOT NULL VARCHAR2(3)
 TRH_WH_CD                                 NOT NULL VARCHAR2(6)
 TRH_TRNTYP                                NOT NULL VARCHAR2(2)
 TRH_TRNNO                                 NOT NULL VARCHAR2(10)
 TRH_TRNDTE                                NOT NULL DATE
 TRH_SC_CD                                          VARCHAR2(3)
 TRH_PRNFLG                                         VARCHAR2(1)
 TRH_USER_ID                               NOT NULL VARCHAR2(8)
 TRH_SYS_DT                                NOT NULL DATE
 TRH_PGRNNO                                         VARCHAR2(20)
 TRH_DOCNO                                          VARCHAR2(45)
 TRH_DOCDTE                                         DATE
 TRH_INVDTE                                         DATE
 TRH_INVNO                                          VARCHAR2(10)
 TRH_LRNO                                           VARCHAR2(15)
 TRH_LRDTE                                          DATE
 TRH_PONO                                           VARCHAR2(10)
 TRH_PODTE                                          DATE
 TRH_JO_NO                                          VARCHAR2(10)
 TRH_JODTE                                          DATE
 TRH_REMARKS                                        VARCHAR2(200)
 TRH_CSM_TYPE                                       VARCHAR2(3)
 TRH_CSM_CD                                         VARCHAR2(10)
 TRH_FLG1                                           VARCHAR2(1)
 TRH_FLG2                                           VARCHAR2(1)
 TRH_TST_CODE                                       VARCHAR2(3)
 TRH_RSN_TYPE                                       VARCHAR2(2)
 TRH_RSN_CD                                         VARCHAR2(5)
 TRH_TRP_TYPE                                       VARCHAR2(3)
 TRH_TRP_CD                                         VARCHAR2(10)
 TRH_AUTHOR                                         VARCHAR2(15)
 TRH_PREPARE                                        VARCHAR2(15)
 TRH_VEHNO                                          VARCHAR2(15)
 TRH_TOTBAGS                                        NUMBER(5)
 TRH_TRIPST_NO                                      VARCHAR2(10)
 TRH_TRIPST_DT                                      DATE
 TRH_QTY                                            NUMBER(7)
 TRH_EMP_CD                                         VARCHAR2(6)
 TRH_NET_WEIGHT                                     NUMBER(13,3)
 TRH_REMARKS2                                       VARCHAR2(200)

As said earlier this is a Prodcution & Bussiness intensive table on which there is seldom or NO DDL statements are executed.
Pls help me in solving this problem..

In the example that you have given, I wonder if the link to the production system is being dropped and recreated?  This is an example of DDL on the *distributor* system, rather than the production system, that might cause the problem.

One way to solve this problem is to avoid it.  Instead of using a materialized view, why not simply use a standard table and then to refresh say:



Do other experts agree?
ILCLAuthor Commented:
Dear Tony,
I have done all preliminary investigation like any DDL statements on the base table, Pinging the service of both DB from both DBs, Status of DB link, both database open etc......
And I have even tried to recompiling MVs in the Distributor DB but the tool (SQL plus or OEM) in whichever I recompile will hang indefinitely (some times 3-5 days) and would not recompile. In such cases I have to terminate the application and have to drop and recreate the MVs.
I have tried every thing possible by me. Now I want a real solution for this problem.

Can anyone help me in this???? pls
Do you know when the MVs become invalid? Is it always at the same time each time? Try to correlate this with other activities. eg it might coincide with a weekly batch job or something. I would setup a script that periodically checks the compile status of the MVs and notifies you when it changes.

Just a thought to try and narrow down the cause of the problem.
Hi Vijay,

Yes it would be helpful to know when the MV becomes invalid as Mr Ned pointed out. My guess is that it becomes invalid immediately following the refresh, can you confirm that ?

Another question I had was why you are enabling Query Rewrite on this MV ? This is not a fast refreshable MV and also it does not have any WHERE clause, it is a complete copy of the master table. Have you tried recreating the MV without the "ENABLE QUERY REWRITE" line ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Greg CloughSenior Oracle DBACommented:
I know this is an old question, but I just ran across a very similar problem:

* Materialized view that would go invalid... just so happens after every refresh attempt
* View was FAST refreshable

The problem was that the Materialized View Log was missing, so the refresh was failing. The solution was to re-create the Log, then perform a full refresh. After that, the fast refreshes worked just fine.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.