?
Solved

Materialized view not refreshing

Posted on 2004-10-29
20
Medium Priority
?
981 Views
Last Modified: 2007-12-19
Hi all Oracle guys,
I build a materalized view with refresh complete cause it is based on a complex view.
However, the view does not refreshes, I tried a very simple statement

CREATE MATERIALIZED VIEW MVW_BOM
         REFRESH COMPLETE
      START WITH TO_DATE('29-Oct-2004 04:25:00 PM','dd-Mon-yyyy HH:MI:SS AM')
      NEXT  SYSDATE + 1/240
      WITH PRIMARY KEY
      AS SELECT * FROM VW_PSG_BOMCHECK


The time on server when I created it was 4:22:18 PM, and I waited till 4:26:00 and the view was not refreshed.
However, If I tried it on another server, it does work well with the same sql, can anyone please tell me whether there is a setting in the DB server itself to control this.
Urgent as i have dial up to the company every day 8am :-(


0
Comment
Question by:monjoes
  • 8
  • 5
  • 4
  • +2
19 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12442382
It seems strange - but does the time match your condition ?
0
 

Author Comment

by:monjoes
ID: 12442406
Yes, it does, now every MView in the DB is not refreshing anymore,,.....
have to do it manually everyday....
cannot last for too long
0
 

Author Comment

by:monjoes
ID: 12470652
Come on guys, please help, I know it is difficult but there must be someone know the reason.
Does it do anything with the tablespace size?
It is exactly the same statement.....
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 22

Expert Comment

by:Helena Marková
ID: 12470764
Are there any errors there ?
0
 

Author Comment

by:monjoes
ID: 12470804
No error at all, nothing happened...
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12470928
What is your Oracle version ?
0
 

Expert Comment

by:VX70
ID: 12552443
Try this .. replace the USERNAME, TABLENAME and TABLESPACE with yours ..

CREATE MATERIALIZED VIEW "USERNAME"."TABLENAME"
TABLESPACE "TABLESPACE"
BUILD IMMEDIATE  
USING INDEX
TABLESPACE "TABLESPACE"  
REFRESH FORCE
START WITH to_date('29-Oct-2004 04:25:00 PM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 1/1440
AS
SELECT * FROM USERNAME.TABLENAME


Hope it helps

VX
0
 
LVL 5

Expert Comment

by:alexfrl
ID: 12555294
In the case of building the view in more then 6 minutes you get a time overlapping
You force refreshing when the process is still running. Then, due to the error (usually not shown) there is no more auto refresh.

you can check it by trying this one:


CREATE MATERIALIZED VIEW MVW_BOM
        REFRESH COMPLETE
     START WITH TO_DATE('29-Oct-2004 04:25:00 PM','dd-Mon-yyyy HH:MI:SS AM')
     NEXT  SYSDATE + 1/(2*24) -- refresh each 30 minutes
     WITH PRIMARY KEY
     AS SELECT * FROM VW_PSG_BOMCHECK
0
 

Author Comment

by:monjoes
ID: 12562423
Thanks all,
Thanks for giving your help , I 've tried VX70 's solution but it didn't quite work,
however I don't understand alexfrl 's answer, the view I've given here takes 10 seconds to retrieve in full, I am using as a test. but how does your sql different? can you please give more information?
Thanks
Joey
0
 
LVL 5

Expert Comment

by:alexfrl
ID: 12563012
I supposed that your view runs long
Therefore it cannot complete before the start of next refreshing (which starts in a given time gap from the start of a previous refreshing). It usually causes error and stops the rescheduling.
But you say it runs 30 seconds. It much less then 6 min.
30 sec! Why do you need this materialized view - it is quite a good speed - use it as a regular view :)))))

with 30 seconds of full 1st building (!) my supposition does not apply to your case!

-- alexfrl

0
 

Author Comment

by:monjoes
ID: 12563033
Hi Henka,
The Oracle version from sqlplus:


Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.


SQL*Plus: Release 8.1.7.0.0 - Production on Fri Nov 12 14:47:48 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production


Joey
0
 

Author Comment

by:monjoes
ID: 12563037
Hi, alexfrl,
There may be some misunderstanding, the one that runs for 30 secs is a testing mview only, but even for 30 secs it does not work.....
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12563047
I don't know why it is not refreshing. I think that alexfrl's advice "... - use it as a regular view :))))) " is a very good answer..
0
 
LVL 5

Expert Comment

by:alexfrl
ID: 12567602
How long time does it take you to create your materialized view or how long does "select count(1) from <your query>" last?
0
 

Author Comment

by:monjoes
ID: 12589691
Hi, Alexfrl, about 10 seconds
0
 
LVL 5

Expert Comment

by:alexfrl
ID: 12594675
if you mean  10 seconds of full query "select count(1) from <your query>" then:
1. You do not need any Mviews
2. My "theory" doesn't work in your case. Sorry.
0
 

Author Comment

by:monjoes
ID: 12610454
Hi, guy
relieved, :-)
I found the problem is caused by the parameter in init.ora.
the job-queue_process was set to 0 that prohibits any background scheduled jobs to be run, when I reset it to 4 by using alter system set job_queue_process =4;
then the views can be run..

Thanks for all your advices
Joey
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12611938
It is fine that you have solved the problem. You can ask a 0-point question in a Support topic for moving this question to PAQs and refunding points to you.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12828154
PAQed with no points refunded (of 125)

modulo
Community Support Moderator
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

607 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