Re: Materialized views are not getting refreshed

Hey Thanks alot Frank,
It really worked, and for me an addition in my DBA knowledge..:-) I guess the culprit was job_queue_processes as 1st i putted the nls_date_format and checked it 1ce ..it didn't worked then i putted the parameter to 3 and it worked ..like a charm... thanks 1ce again,
Takecare ,
Prashant.
Frank <fbortel_at_home.nl> wrote in message news:<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?
Received on Wed Nov 12 2003 - 08:04:18 CET