Home » SQL & PL/SQL » SQL & PL/SQL » Materialised views not refreshing (Oracle 11g Release
Materialised views not refreshing [message #655885] Thu, 15 September 2016 03:42 Go to next message
Messages: 34
Registered: August 2012
Location: United Kingdom
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)

These are in a group and are refreshed as follows (with no error returned)


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: 1802
Registered: December 2004
Senior Member
Are you sure that the mat_mview1 MVIEW is in MV_REFRESH_GROUP?


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

[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: 65849
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 Oct 21 22:11:56 CDT 2018