Materialized views are not getting refreshed

[Prashant]

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

kh*************@rediffmail.com too , Thanks alot in advance,
Prashant Khanna

[Frank]

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

kh*************@rediffmail.com too , Thanks alot in advance,
Prashant Khanna

I miss the Generate MV support part....

--
Regards, Frank van Bortel

[Prashant]

Hi Frank,
Can u please go in more detail , iam still searching for the solution...thanks!
Frank <fb*****@home.nl> wrote in message news:<bo**********@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

kh*************@rediffmail.com too , Thanks alot in advance,
Prashant Khanna

I miss the Generate MV support part....

[Frank]

Prashant wrote:

Hi Frank,
Can u please go in more detail , iam still searching for the solution...thanks!
Frank <fb*****@home.nl> wrote in message news:<bo**********@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

kh*************@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

[Prashant]

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 <fb*****@home.nl> wrote in message news:<bo**********@news2.tilbu1.nb.home.nl>...

Prashant wrote:

Hi Frank,
Can u please go in more detail , iam still searching for the solution...thanks!
Frank <fb*****@home.nl> wrote in message news:<bo**********@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

kh*************@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?