Materialized view becomes invalid even compiling right

[joao.alberto.gt]

Environment information:
* Oracle version: 9i and 10g
* Operating system: Linux and Windows

Personal, good morning.

I am using views materialized for a BD data extraction process. Views usually compile, without fail, but after a while they appear invalid in the bank, I recompile without changing anything and works, but a while later reassemes as invalid objects. And the time later it's time even, it gets longer than 1 hour. Does anyone have any idea what it can be?

Hugs.

John.

[dr_gori]

Did anyone have not changed any object that ended up leaving the invalid view?
It is very common when we change any program or object, all dependent objects are invalid. And you need to recompile them.

See this table dba_dependencies shows all dependents of an object ...

[joao.alberto.gt]

Structural change did not have any. Only content changes to the tables used by the materialized view. Could this affect too?

[dr_gori]

No, change data does not affect object status.

But see: I found a document that talks about a bug 2639679.
http://it.toolbox.com/blogs/david/mater ... ound-23095

Select all

 Materialized view will become invalid after a refresh - documented Oracle bug in older versions+ workaround. 
 
David Yahalom (Senior Project Manager) posted 3/16/2008 
 
Today I stumbled across a very annoying Oracle bug regarding Materialized Views becoming invalid after a refresh. I'm talking about Oracle Bug 2639679 (QUERY_REWRITE flag changes for MVIEW with DATE RANGE in WHERE) which affects Oracle 8i and even some versions of 9i (can't be sure which versions exactly as Metalink says this is resolved in 9i but clearly this is not the case). 
 
When you create a materialized view with DISABLE QUERY REWRITE option, when you refresh the materialized view (using DBMS_SNAPSHOT.REFRESH, DBMS_REFRESH.REFRESH or any other method), the QUERY REWRITE flag will automatically be turned back on. This is a problem if your materialized view has a where clause in it as QUERY REWRITE + a where clause is a no-no. 
 
This bug will cause your materialize view to become invalid every time you refresh it. Very annoying. 
 
The solution I found was to add a "disable query rewrite" command before and after the refresh of the materialized view. 
 
EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite'); 
DBMS_SNAPSHOT.REFRESH( 'MV_NAME','C'); 
EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite'); 
 
You can put this in a database job to have the materialized view refresh automatically. 
 
Hope this helps.

[joao.alberto.gt]

Speak Thomas, beleza?

Dude, I executed the tip that you passed but nothing. The materialized view continues to appear invalid in the bank, after some time. This does not disturb your execution at all, but visually becomes invalid. I tried everything already expensive and nothing, I have no idea what it can be. Any light?

John.

[joao.alberto.gt]

Searching a little more I discovered materialized view is invalid when your base table receives some update. I read something about changing the query_integrity to stale_tolerated, causing Oracle to accept the view with outdated information, but even so it did not work.