Materialised views not refreshing [message #655885] |
Thu, 15 September 2016 03:42 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6245e07fb26043a159d370ab0821704a?s=64&d=mm&r=g) |
brown_zzz
Messages: 39 Registered: August 2012 Location: United Kingdom
|
Member |
|
|
We have a set of materialised views created on a remote database (using links to the source database) as
create materialized view mat_view1(DATETIME_FROM,VALUE)
AS
(SELECT * FROM
(SELECT * FROM data_table1@DATABASE_LNK_TO_SOURCE
)
);
These are in a group and are refreshed as follows (with no error returned)
EXEC DBMS_REFRESH.REFRESH('MV_REFRESH_GROUP');
Intermittently (about 1 in 10 times) the view says it's refreshed but the data had not been. i.e.
the last refresh time is correct from "SELECT last_refresh_date FROM all_mviews WHERE mview_name = x"
but the data is not.
Calling the views individually without the group makes no difference.
Calling the refresh twice seems to work, but not an ideal solution.
|
|
|
Re: Materialised views not refreshing [message #655985 is a reply to message #655885] |
Mon, 19 September 2016 08:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Are you sure that the mat_mview1 MVIEW is in MV_REFRESH_GROUP?
run
select owner,name from ALL_REFRESH_CHILDREN where rname = 'MV_REFRESH_GROUP';
Also you MVIEW create command isn't correct. Use
create materialized view mat_view1
AS
SELECT DATETIME_FROM,VALUE FROM data_table1@DATABASE_LNK_TO_SOURCE;
[Updated on: Mon, 19 September 2016 08:22] Report message to a moderator
|
|
|
|