472,988 Members | 2,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,988 software developers and data experts.

Materialized views are not getting refreshed

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
Jul 19 '05 #1
4 13484
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

Jul 19 '05 #2
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....

Jul 19 '05 #3
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

Jul 19 '05 #4
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?

Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Vincent LIDOU | last post by:
Do not trust values returned by materialized views under SQL Server without frequently checking underlying tables!!! I already posted this message under microsoft.public.sqlserver.server and I'm...
6
by: Bruce | last post by:
I want to create a new table based on an existing table, but I don't want the tables to have any enforced relationship. Is this possible without having to do a CREATE TABLE and an INSERT? ...
7
by: Esteban Kemp | last post by:
PostgreSql support materialized views ??? if not, there is something similar?? Thanks Esteban kemp
4
by: marklawford | last post by:
I'm looking to materialize out a pretty complex query/view to facilitate retrieval of the underlying data for presentation to a screen. Given the table sizes and a few left outer joins the query...
1
by: rumasinha | last post by:
Hi, I first created a materialized view that was based on the org striped views like po_headers hence the automatic refresh was not happening. After automatic complete refresh, the materialized...
4
by: Prashant | last post by:
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)...
1
by: Pink Panther | last post by:
Hi All, Sorry this may be a repost as first attempt died on me... I have a set of 50 materialized views that are populated every morning. However 2 of the materialized views contain no...
3
by: jonceramic | last post by:
Hi All, I need to know the best way to set up a datawarehouse/materialized view for doing statistics/graphs in Access. My crosstabs and unions are getting too complicated to crunch in real...
3
by: kewldotnet | last post by:
I have a query thats taking long time to execute. So i have created a Materialized view to refresh it every hour. But when the Materialized view is being refreshed, there is no data in the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.