OraFAQ Forum: SQL & PL/SQL » Materialised view won't refresh

Home » SQL & PL/SQL » SQL & PL/SQL » Materialised view won't refresh (Oracle 10.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialised view won't refresh [message #645725]

Mon, 14 December 2015 04:47

brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom

Member

I have a materialised view that isn't refreshing and I can see this from the last refresh date on all_mviews, the underlying data has definitely changed. Looking at all_mviews table, the view is created as force refresh on commit. The columns from all_mviews are:

OWNER MVIEW_NAME CONTAINER_NAME QUERY_LEN UPDATABLE UPDATE_LOG MASTER_ROLLBACK_SEGCRD V_GEN_CONTACT2 V_GEN_CONTACT2 2436 N NULL NULL

MASTER_LINK REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD
NULL Y GENERAL COMMIT FORCE

BUILD_MODE FAST_REFRESHABLE LAST_REFRESH_TYPE LAST_REFRESH_DATE STALENESS
IMMEDIATE DIRLOAD_DML COMPLETE 19-NOV-15 IMPORT

AFTER_FAST_REFRESH UNKNOWN_PREBUILT UNKNOWN_PLSQL_FUNC UNKNOWN_EXTERNAL_TABLENA N N N

UNKNOWN_CONSIDER_FRESH UNKNOWN_IMPORT UNKNOWN_TRUSTED_FD COMPILE_STATE USE_NO_INDEX STALE_SINCE
N Y N VALID N 09-DEC-15

Not sure what the "import" value for the "staleness" columns means.

What is very strange is that the all_mviews column refresh_mode = COMMIT but when I look at the mview definition in SQLdeveloper (by clicking on the SQL tab for the mview) it says "REFRESH FORCE ON DEMAND" as part of the view definition, the "details" tab matches all_mviews and has a value of "COMMIT" for refresh mode.

Obviously I can force a refresh but this doesn't explain the problem.

Thanks for any help

Report message to a moderator

Re: Materialised view won't refresh [message #645730 is a reply to message #645725]

Mon, 14 December 2015 04:56

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 80 characters when you format.
Use SQL*Plus and copy and paste your session, the WHOLE session.

Report message to a moderator

Re: Materialised view won't refresh [message #645751 is a reply to message #645725]

Mon, 14 December 2015 08:53

JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar

Member

Try dbms_mview.explain_mview
It probably will give you more info, about what's wrong

Report message to a moderator

Re: Materialised view won't refresh [message #645760 is a reply to message #645751]

Mon, 14 December 2015 11:08

brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom

Member

To use that properly I have to create a table in the schema and don't have permission Sad

but thanks for the tip

Report message to a moderator

Re: Materialised view won't refresh [message #645761 is a reply to message #645760]

Mon, 14 December 2015 13:34

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If you don't have the permissions to make a table, how do you expect the MVIEW to be created?

Report message to a moderator

Re: Materialised view won't refresh [message #645788 is a reply to message #645761]

Tue, 15 December 2015 03:12

brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom

Member

I'm supporting a live system here and the view was created months ago but has not been refreshing. I'm trying to find out why without more releases of creating tables etc that might not solve the problem.

Report message to a moderator