[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Materialized view not refreshing

Posted on 2004-10-29
20
Medium Priority
?
977 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +2
20 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

650 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