Home » Server Options » Replication » Materialized view does not refresh (Oracle 9.2.0.8.0)
Materialized view does not refresh [message #347617] Fri, 12 September 2008 06:38 Go to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
Hello

I have attempted to set up the materialized view below to refresh complete at the time specified below. The problem is that it does not refresh and pick up the changes to one of the base tables.

When I issue the command:
exec DBMS_REFRESH.REFRESH('TEAMSITE3.FIRMSEARCH_COWS_MV ');

the refresh works..

Any reason why this view is not refreshing at the time stated??
Is it a question of privileges for this user?
Thanks

CREATE MATERIALIZED VIEW FIRMSEARCH_COWS_MV
REFRESH COMPLETE START WITH to_date('11-09-2008 10:00:00','dd-MM-yyyy hh24:mi:ss') NEXT (SYSDATE+1)
AS SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;

Report message to a moderator

Re: Materialized view does not refresh [message #347621 is a reply to message #347617] Fri, 12 September 2008 06:58 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


You got any error?

If NO. Try this?

Quote:
exec DBMS_MVIEW.REFRESH('TEAMSITE3.FIRMSEARCH_COWS_MV','C');


&

Please read the below links before posting.



Babu

Report message to a moderator

Re: Materialized view does not refresh [message #347622 is a reply to message #347617] Fri, 12 September 2008 07:02 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
Hello

Thanks for the reply.

I tried:

exec DBMS_MVIEW.REFRESH('TEAMSITE3.FIRMSEARCH_COWS_MV','C');
and it works fine without errors.

My question is why is the timed refresh not working..

Thanks

Report message to a moderator

Re: Materialized view does not refresh [message #347625 is a reply to message #347622] Fri, 12 September 2008 07:27 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Again,

1. Very first time what error message you faced during materialized view refresh?

Quote:
REFRESH COMPLETE


Materialized view refresh type Complete. So; You must be use Complete (C) option during refresh.

Quote:
My question is why is the timed refresh not working.


Again; I'm asking you what error you have faced??

Babu

Report message to a moderator

Re: Materialized view does not refresh [message #347635 is a reply to message #347617] Fri, 12 September 2008 08:11 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
When I first created this view I had no errors reported.

I think there may be a misunderstanding here..

Note: The materialized view I created did so without errors.
My problem is that the view does not refresh at the times I have specified ie.

REFRESH COMPLETE START WITH to_date('11-09-2008 10:00:00','dd-MM-yyyy hh24:mi:ss') NEXT (SYSDATE+1)
Is there anything wrong with this statement?

It only refreshes when I issue the command:

exec DBMS_MVIEW.REFRESH('TEAMSITE3.FIRMSEARCH_COWS_MV','C');

Is there anything more simpler I could try to check that a timed complete refresh actually works? Any SQL commands I can issue to check there is nothing wrong with the refresh job settings for this view?

Thanks

Report message to a moderator

Re: Materialized view does not refresh [message #347688 is a reply to message #347635] Fri, 12 September 2008 15:40 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
No Issues.

You can continue with as per your materialized view. No need manual refresh.

Babu

Report message to a moderator

Re: Materialized view does not refresh [message #348080 is a reply to message #347617] Mon, 15 September 2008 10:16 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
Hello

I do not understand your latest update:

No Issues.

You can continue with as per your materialized view. No need manual refresh.



What does this mean? If I try to create the materialized view
on a timed refresh, it does not refresh with the base tables!
This is my issue. Any ideas or can you provide me with some
examples I could try just paste to get the view to refresh on a timed basis just to see if it works at least!!

I re-interate, calling the command line refresh statement works.

Thanks

Report message to a moderator

Re: Materialized view does not refresh [message #348849 is a reply to message #348080] Thu, 18 September 2008 02:06 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
Sorry for delay response.

Check this & let me know your comments. Refresh done by every minutes.

SQL> Create table Babu (A int);

Table created.

SQL> Create Materialized view babu_mv refresh complete start with (sysdate) next  (sysdate+1/1440) with rowid
  2  as select * from babu;

Materialized view created.

SQL> select count(*) from babu;

  COUNT(*)
----------
         0

SQL> select count(*) from babu_mv;

  COUNT(*)
----------
         0

SQL> begin
  2  for i in 1..10 loop
  3  insert into babu values (i+1);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from babu;

  COUNT(*)
----------
        10

SQL> select count(*) from babu_mv;

  COUNT(*)
----------
         0


SQL> select to_char(sysdate,'hh:mi') from dual;

TO_CH
-----
08:02

SQL> /

TO_CH
-----
08:03

SQL> select count(*) from babu;

  COUNT(*)
----------
        10

SQL> select count(*) from babu_mv;

  COUNT(*)
----------
        10


Babu

Report message to a moderator

Re: Materialized view does not refresh [message #349605 is a reply to message #347617] Mon, 22 September 2008 06:11 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
Only had a chance to look at your reply recently. Thanks.

I replicating your example to my particular materialized view which joins across several base tables:

CREATE MATERIALIZED VIEW FIRMSEARCH_COWS_MV
REFRESH COMPLETE START WITH (SYSDATE) NEXT (SYSDATE+1/1440) WITH ROWID
ENABLE QUERY REWRITE
AS SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;

When I type:
select * from FIRMSEARCH_COWS_MV;

I get 8 rows returned.

And when I run the main part of query which makes up the mat view :
SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;

I get 8 rows also.


Now when I update a value in one of these base tables:SOL_PANELS_AOL_REF , and then run:

SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;

I now get 7 rows returned.

But now when I run the the materialized view query again:

select * from FIRMSEARCH_COWS_MV;

I still get 8 rows returned. When it should have refreshed to 7 rows after one minute! Is this not correct??

What am I doing wrong?

Is there an Oracle setting I have missed which needs to be set to enable materialized view refresh?

Report message to a moderator

Re: Materialized view does not refresh [message #349615 is a reply to message #349605] Mon, 22 September 2008 06:35 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Thanks.

I want to know two things..

1.

>>Now when I update a value in one of these base tables:SOL_PANELS_AOL_REF , and then run:

>>I now get 7 rows returned.

You done UPDATE or DELETE ?

2.

REFRESH COMPLETE START WITH (SYSDATE) NEXT (SYSDATE+1/1440)


Materialized view refresh every one min only. Once again confirm me have you checked after one minute?

3.

Try this.

1. insert some few values in base table.
2. Create Materialized view.
3. Execute below query in base table & materialized view confirm me total number of records.

SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;


4. Now try to insert/delete some records in base table.

5. From Base table. Using below query send me total number of records.

SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;


6. After 1 minute try this & let me know.

SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
SOL_PANELS_AOL_REF spar,
LV_CATEGORIES_OF_WORK cow,
LV_ORGANISATIONS O
WHERE
rio.ORGA_ID = O.ID
AND rio.BODY_ID = lra.BODY_ID
AND lra.RAT_CODE = spar.RAT_CODE
AND spar.COW_REF = cow.REF;

select count(*) from FIRMSEARCH_COW;

Report message to a moderator

Re: Materialized view does not refresh [message #350173 is a reply to message #347617] Wed, 24 September 2008 05:13 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
Hello

Thanks for the reply. I have responded in line to your request:

I want to know two things..

1.

>>Now when I update a value in one of these base tables:SOL_PANELS_AOL_REF , and then run:

>>I now get 7 rows returned.

You done UPDATE or DELETE ? This was an update of a row on the base table SOL_PANELS_AOL_REF. Should this make a difference?

2.

REFRESH COMPLETE START WITH (SYSDATE) NEXT (SYSDATE+1/1440)



Materialized view refresh every one min only. Once again confirm me have you checked after one minute?

Yes, I can confirm I checked after one minute, then 5 mins etc etc.

I will now try to action the rest of your post. And get back to you. But can you please respond to my replies above. Thanks.

Report message to a moderator

Re: Materialized view does not refresh [message #350337 is a reply to message #350173] Wed, 24 September 2008 15:20 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Good..

Okay. now my final conculsion your contion expression in materialized view.

Can you test third steps from my previous post.

let us know.

Babu

Report message to a moderator

Re: Materialized view does not refresh [message #350476 is a reply to message #350337] Thu, 25 September 2008 03:25 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
I do not understand your update..

Please respond to my comments highlighted in bold for my previous post.

Thanks

Report message to a moderator

Re: Materialized view does not refresh [message #350650 is a reply to message #350476] Thu, 25 September 2008 16:12 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
Hello,

Quote:
You done UPDATE or DELETE ? This was an update of a row on the base table SOL_PANELS_AOL_REF. Should this make a difference?


Quote:
Yes, I can confirm I checked after one minute, then 5 mins etc etc.


I agree your both statement. There is NO Issues.

Quote:
what about this point???

Quote:
I will now try to action the rest of your post. And get back to you.



Babu

[Updated on: Thu, 25 September 2008 16:26]

Report message to a moderator

Re: Materialized view does not refresh [message #352107 is a reply to message #350650] Mon, 06 October 2008 03:45 Go to previous messageGo to next message
sokstan
Messages: 8
Registered: September 2008
Junior Member
I just picked up your latest update and I am again confused by your statements!

What do you mean by:

I agree your both statement. There is NO Issues

There are issues - my questions where:

1 - Should an update statement run against the base table
SOL_PANELS_AOL_REF make any difference to the refresh on the materialised view? Should it refresh?

2 - Checking the materialised view table after one, two and three
minutes did not make a difference! The view did not refresh!!

Do you know why???


Please respond to these specific points please

Thanks

Report message to a moderator

Re: Materialized view does not refresh [message #354673 is a reply to message #352107] Mon, 20 October 2008 14:06 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Sorry for delay response.

>>minutes did not make a difference! The view did not refresh!!

Please try to understand your Materialized view script.

In your materialized view script your having some contion expression. As per you contion only data's comes from master table.

Babu

Report message to a moderator

Previous Topic: Refreshing the tables in Multimaster replication
Next Topic: Error during addtion of mastersite
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 17 20:28:19 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.