Solved

Materialized view not refreshing

Posted on 2004-10-29
953 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
Question by:monjoes
    19 Comments
     
    LVL 22

    Expert Comment

    by:Henka
    It seems strange - but does the time match your condition ?
    0
     

    Author Comment

    by:monjoes
    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
    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
     
    LVL 22

    Expert Comment

    by:Henka
    Are there any errors there ?
    0
     

    Author Comment

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

    Expert Comment

    by:Henka
    What is your Oracle version ?
    0
     

    Expert Comment

    by:VX70
    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
    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
    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
    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
    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
    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:Henka
    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
    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
    Hi, Alexfrl, about 10 seconds
    0
     
    LVL 5

    Expert Comment

    by:alexfrl
    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
    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:Henka
    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:
    PAQed with no points refunded (of 125)

    modulo
    Community Support Moderator
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    933 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

    18 Experts available now in Live!

    Get 1:1 Help Now