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_e nabled boolean TRUE
query_rewrite_i ntegrity string ENFORCED


i guess both are fine,

now iam not looking to go for fast refreshes and rightnow iam using
" EXEC DBMS_MVIEWS.REF RESH('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 @rediffmail.com too , Thanks alot in advance,
Prashant Khanna

[Frank]

Re: Materialized views are not getting refreshed

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_e nabled boolean TRUE
query_rewrite_i ntegrity string ENFORCED
>
>
i guess both are fine,
>
now iam not looking to go for fast refreshes and rightnow iam using
" EXEC DBMS_MVIEWS.REF RESH('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 @rediffmail.com too , Thanks alot in advance,
Prashant Khanna

I miss the Generate MV support part....

--
Regards, Frank van Bortel

[Prashant]

Re: Materialized views are not getting refreshed

Hi Frank,
Can u please go in more detail , iam still searching for the solution...than ks!
Frank <fbortel@home.n lwrote in message news:<bog4t2$pu k$1@news4.tilbu 1.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_e nabled boolean TRUE
query_rewrite_i ntegrity string ENFORCED


i guess both are fine,

now iam not looking to go for fast refreshes and rightnow iam using
" EXEC DBMS_MVIEWS.REF RESH('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 @rediffmail.com too , Thanks alot in advance,
Prashant Khanna

>
I miss the Generate MV support part....

[Frank]

Re: Materialized views are not getting refreshed

Prashant wrote:

Hi Frank,
Can u please go in more detail , iam still searching for the solution...than ks!
Frank <fbortel@home.n lwrote in message news:<bog4t2$pu k$1@news4.tilbu 1.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_rewrit e_enabled boolean TRUE
>>>query_rewrit e_integrity string ENFORCED
>>>
>>>
>>>i guess both are fine,
>>>
>>>now iam not looking to go for fast refreshes and rightnow iam using
>> " EXEC DBMS_MVIEWS.REF RESH('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 @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
SQLcreate table a(an number);

Table created.

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

SQLselect * from b;
no rows selected

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

SQLselect sysdate from dual;
SYSDATE
-------------------
2003-11-11 21:56:08

SQLselect * from b;
AN
----------
100
101
102

SQLinsert into a values (104);
1 row created.
SQLcommit;
.... Wait a while...
SQLselect * from b;
AN
----------
100
101
102
...nope, not yet
SQL/

AN
----------
100
101
102
104

SQLselect sysdate from dual;

SYSDATE
-------------------
2003-11-11 21:58:02

Do you have job_queue_proce sses set to a positive integer?

--
Regards, Frank van Bortel

[Prashant]

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_proce sses 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@home.n lwrote in message news:<borj4k$8l 4$1@news2.tilbu 1.nb.home.nl>.. .

Prashant wrote:

Hi Frank,
Can u please go in more detail , iam still searching for the solution...than ks!
Frank <fbortel@home.n lwrote in message news:<bog4t2$pu k$1@news4.tilbu 1.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.REF RESH('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 @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
SQLcreate table a(an number);
>
Table created.
>
SQLcreate materialized view b refresh with rowid
2 start with sysdate next sysdate + 1/1440
3 as (select * from a);
Materialized view created.
>
SQLselect * from b;
no rows selected
>
SQLinsert into a values (100);
[Some inserts snipped for brevity]
SQLalter session set nls_date_format ='YYYY-MM-DD HH24:mi:ss';
Session altered.
>
SQLselect sysdate from dual;
SYSDATE
-------------------
2003-11-11 21:56:08
>
SQLselect * from b;
AN
----------
100
101
102
>
SQLinsert into a values (104);
1 row created.
SQLcommit;
... Wait a while...
SQLselect * from b;
AN
----------
100
101
102
..nope, not yet
SQL/
>
AN
----------
100
101
102
104
>
SQLselect sysdate from dual;
>
SYSDATE
-------------------
2003-11-11 21:58:02
>
Do you have job_queue_proce sses set to a positive integer?