Re: Materialized views are not getting refreshed

From: Frank <fbortel_at_home.nl>
Date: Tue, 11 Nov 2003 22:16:38 +0100
Message-ID: <borj4k$8l4$1_at_news2.tilbu1.nb.home.nl>

Prashant wrote:

> Hi Frank,
> Can u please go in more detail , iam still searching for the solution...thanks!
> Frank <fbortel_at_home.nl> wrote in message news:<bog4t2$puk$1_at_news4.tilbu1.nb.home.nl>...
> 

>>Prashant wrote:
>>
>>>Hi I have a problem with refreshing of Mviews , I will narrate every
>>>thing step by step:
>>>
>>>1.create table a (a number);
>>>
>>>2.insert into table a values(&a); (after inserting 2 rows and
>>>commiting)
>>>
>>>3. create MATERIALIZED view b REFRESH WITH ROWID START WITH SYSDATE
>>>NEXT sysdate + 2/1440 as select * from a;
>>>
>>>Mview created
>>>
>>>4. insert into a values(&a); 9after inserting 2 more rows and
>>>commiting)
>>>
>>>5.select * from a;
>>>
>>> 4 rows selected
>>>
>>>6. select * from b;
>>>
>>> 2 rows selected (BUT HERE IT SHOULD BE 4 ROWS)( EVEN WAITING FOR
>>>10 MIN , NOTHING IS COMING)
>>>
>>>
>>>WHATS GOING ON..
>>>
>>>if i do the same on diffrent server it works, but not here..
>>>
>>>then i thought checking some parameters
>>>
>>>the parameters i checked with values are :
>>>
>>>query_rewrite_enabled boolean TRUE
>>>query_rewrite_integrity string ENFORCED
>>>
>>>
>>>i guess both are fine,
>>>
>>>now iam not looking to go for fast refreshes and rightnow iam using
>>> " EXEC DBMS_MVIEWS.REFRESH('B')" to refresh it..
>>>
>>>Can u please help me out, what Iam doing wrong..
>>>
>>>It would be great if u mail me also on my personal mail id
>>>
>>> khanna_prashant_at_rediffmail.com too , Thanks alot in advance,
>>>Prashant Khanna
>>
>>I miss the Generate MV support part....
Forget that remark - you're not using log tables.

Did your scenario, and it works like a charm... Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production
SQL> create table a(an number);

Table created.

SQL> create materialized view b refresh with rowid

   2 start with sysdate next sysdate + 1/1440    3 as (select * from a);
Materialized view created.

SQL> select * from b;
no rows selected

SQL> insert into a values (100);
[Some inserts snipped for brevity]
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:mi:ss'; Session altered.

SQL> select sysdate from dual;
SYSDATE

---

2003-11-11 21:56:08

SQL> select * from b;

         AN

---

        100
        101
        102

SQL> insert into a values (104);
1 row created.
SQL> commit;
... Wait a while...
SQL> select * from b;

         AN

---

        100
        101
        102

..nope, not yet
SQL> /          AN

---

        100
        101
        102
        104

SQL> select sysdate from dual;

SYSDATE

---

2003-11-11 21:58:02

Do you have job_queue_processes set to a positive integer?

-- 
Regards, Frank van Bortel

Received on Tue Nov 11 2003 - 22:16:38 CET