Materialized view not refreshing

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 :-(


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

Helena Markováprogrammer-analystCommented:
It seems strange - but does the time match your condition ?
0
monjoesAuthor Commented:
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
monjoesAuthor Commented:
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
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.

Helena Markováprogrammer-analystCommented:
Are there any errors there ?
0
monjoesAuthor Commented:
No error at all, nothing happened...
0
Helena Markováprogrammer-analystCommented:
What is your Oracle version ?
0
VX70Commented:
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
alexfrlCommented:
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
monjoesAuthor Commented:
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
alexfrlCommented:
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
monjoesAuthor Commented:
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
monjoesAuthor Commented:
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
Helena Markováprogrammer-analystCommented:
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
alexfrlCommented:
How long time does it take you to create your materialized view or how long does "select count(1) from <your query>" last?
0
monjoesAuthor Commented:
Hi, Alexfrl, about 10 seconds
0
alexfrlCommented:
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
monjoesAuthor Commented:
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
Helena Markováprogrammer-analystCommented:
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
moduloCommented:
PAQed with no points refunded (of 125)

modulo
Community Support Moderator
0

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

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.