[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Why Materialized views' status frequently go to NEEDS COMPILE

Posted on 2004-11-23
Medium Priority
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

Author Comment

ID: 12662603
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

ID: 12666923
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.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.


Author Comment

ID: 12672647
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..


Expert Comment

ID: 12676783
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

ID: 12678477
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

Expert Comment

ID: 12684951
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

SDutta earned 1000 total points
ID: 12697131
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 ?

Expert Comment

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month19 days, 6 hours left to enroll

834 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