Why Materialized views' status frequently go to NEEDS COMPILE

Posted on 2004-11-23
Last Modified: 2008-01-09
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
Question by:ILCL
    LVL 12

    Expert Comment


    Author Comment

    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

    LVL 10

    Expert Comment

    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.

    Author Comment

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

    LVL 2

    Expert Comment

    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?

    Author Comment

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

    Expert Comment

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

    Accepted Solution

    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 ?
    LVL 6

    Expert Comment

    by:Greg Clough
    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now