Materialised views not refreshing [message #655885] |
Thu, 15 September 2016 03:42 |
|
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 |
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
|
|
|
|