Home » SQL & PL/SQL » SQL & PL/SQL » Materialised views not refreshing (Oracle 11g Release 11.2.0.4.0)
Materialised views not refreshing [message #655885] Thu, 15 September 2016 03:42 Go to next message
brown_zzz
Messages: 34
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 messageGo to next message
Bill B
Messages: 1718
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

Re: Materialised views not refreshing [message #655997 is a reply to message #655885] Mon, 19 September 2016 11:47 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

And feedback, most of your previous topics are still pending waiting for you.

Previous Topic: DECLARE a FUNCTION
Next Topic: Missing Record in running Total
Goto Forum:
  


Current Time: Sun Feb 25 22:06:24 CST 2018

Total time taken to generate the page: 0.01296 seconds