Why do materialized views show a red X (invalid) in Mat. View mode (only) on the left and as INVALID when referenced in the Used By tab on the right? Both the Object and Accessing views appear perfectly fine and return the data correctly. This seems fairly consistent and is not resolved with nightly refreshes. I’m getting to the point where I think this is safe to ignore, but I thought I’d ask before I officially Don’t Care. Thanks for any wisdom … (and Happy Holidays.)
Dave
This is the query that we run in the USED BY tab:
with Objs as
(Select object_id
from (select object_id, referenced_object_id
from public_dependency
where referenced_object_id <> object_id) pd
connect by nocycle prior object_id = referenced_object_id
start with referenced_object_id = :ObjID)
Select o.owner, o.object_type, o.object_name, o.object_id, o.status
from sys.DBA_OBJECTS o, Objs
where o.Object_id = Objs.Object_id;
So, Oracle must be showing your Materialized views as invalid in DBA_OBJECTS. This can happen is if you’ve done any DDL on any of the objects referenced in the MView’s query.
Thank you John. I’m not sure that’s the issue here as the invalid state seems to persist even through a MView refresh. These views are designed to transform, stabilize and version production data for reporting in a mobile app. The initial and somewhat complex transformations are handled in series of standard views that can then be replicated in the MView with little more than a SELECT * query. I do have a live sysdate-based timestamp in the view data that gets posted to the MView. Could that be the source of Invalid-ness? Otherwise, the data is a pretty stable and doesn’t change much from day to day, and the MView, itself, performs as expected. However, If I understand you correctly, maybe I don’t care because the content of the MView is the snapshot from 3:30a, e.g. yesterday’s final state, and that’s what I want. Make sense?
Thanks for the help.
Dave
Hey Dave, I don’t think it would be caused by SYSDATE in one of the underlying views. But it could be one of the tables that are underneath the views that is causing the INVALIDness. Maybe there is some partition management happening on one of the tables? Just a guess, of course, but something like that could do it too, I think.
Hey John,
(Re: sysdate) … even when it is loaded to the MView and thus, automatically become out of sync with its source? I cannot think of anything else because the rest of the content is either a direct import of source view columns or a few case statements setting flags based on other (static) data on that row …? I could pull the RFRSHD (date) field off the view and just load SYSDATE to the MView and see what happens. Otherwise, I’m inclined to move on since the data checks out just fine … but I will keep an eye on it. Thanks,
Dave
Is it because:
Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.
http://www.orafaq.com/forum/t/127268/
The above will not affect REFRESH ON COMMIT mviews as they get refreshed when the changes to the underlying table sre, ahem, committed.
HTH
Cheers,
Norm. [TeamT]
–
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Thanks Norm,
What I think you are describing is exactly the issue (data consistency, not a faulty view construct) and therefore, is one that I don’t think I care about. I refresh (On Demand) nightly …
REFRESH ON DEMAND
START WITH sysdate
NEXT trunc(sysdate+1)+3.5/24
… to stabilize and snapshot-ize the data so I can put it in memory. I don’t care that it changes over the next 24 hours, because I’ll do it again. Thus, the invalid-ness (I’m learning) seems more in relation to data currency than its construct so I think I’m ok, aren’t I?
Thanks to both you guys for the contributions to my education.
Dave
Hi Dave,
You are welcome. I had similar problems a few years back with exactly this problem. Took me ages to work out the solution - which I then forgot about as time went by. Old age!
As you are refreshing on demand, you will see the problem any time the underlying tables change, but unless you need up to the second accuracy, this is not a problem.
You are good to go - and have nothing to worry about.
Cheers,
Norm. [TeamT]
–
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Good morning! With this “X” I understand. But there is " ! " in front of some of Materialized views. It goes away if I recompile this MV. Does it means it needs recompilation? Why that happened?
Thank you. Jenny
The 2nd to last toolbar button on the Schema Browser gives you a legend of all of the icons. The ! icon for materialized views indicates “needs compile”. This information comes from the COMPILE_STATE of DBA_MVIEWS. You can read about that in Oracle docs, here: https://docs.oracle.com/database/121/REFRN/GUID-8B9432B5-6B66-411A-936E-590D9D7671E9.htm#REFRN20139
-John
Thank you.
On Thu, Jan 11, 2018 at 8:54 AM, John Dorlon bounce-jdorlon@toadworld.com wrote:
RE: Schema Browser: Why do Materialized Views show INVALID when in Mat. View mode?
Reply by John Dorlon
The 2nd to last toolbar button on the Schema Browser gives you a legend of all of the icons. The ! icon for materialized views indicates "needs compile". This information comes from the COMPILE_STATE of DBA_MVIEWS. You can read about that in Oracle docs, here: https://docs.oracle.com/database/121/REFRN/GUID-8B9432B5-6B66-411A-936E-590D9D7671E9.htm#REFRN20139
-John
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Jenny Ostrovsky
Sr. Database Administrator
ITS | ECOMS Services
NEW YORK UNIVERSITY
212-992-7775
To follow up on whatbJohn said, an MV will "need compile" each and every time that the underlying tables change - alter table etc, or, insert, update, delete etc. This is true if the MVs are refresh on demand. If they are refresh on commit, then you won't see the '!' for insert, update, delete actions on the underlying table as the commit will refresh those parts of the MV deemed necessary to update.
HTH
Cheers,
Norm. [TeamT]
On 11 January 2018 13:53:51 GMT+00:00, John Dorlon bounce-jdorlon@toadworld.com wrote:
RE: Schema Browser: Why do Materialized Views show INVALID when in Mat. View mode?
Reply by John Dorlon
The 2nd to last toolbar button on the Schema Browser gives you a legend of all of the icons. The ! icon for materialized views indicates "needs compile". This information comes from the COMPILE_STATE of DBA_MVIEWS. You can read about that in Oracle docs, here: https://docs.oracle.com/database/121/REFRN/GUID-8B9432B5-6B66-411A-936E-590D9D7671E9.htm#REFRN20139
-John
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Thank you.
On Thu, Jan 11, 2018 at 9:19 AM, Norm [TeamT] bounce-NormTeamT@toadworld.com wrote:
RE: Schema Browser: Why do Materialized Views show INVALID when in Mat. View mode?
Reply by Norm [TeamT]
To follow up on whatbJohn said, an MV will "need compile" each and every time that the underlying tables change - alter table etc, or, insert, update, delete etc. This is true if the MVs are refresh on demand. If they are refresh on commit, then you won't see the '!' for insert, update, delete actions on the underlying table as the commit will refresh those parts of the MV deemed necessary to update.HTH
Cheers,
Norm. [TeamT]
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Jenny Ostrovsky
Sr. Database Administrator
ITS | ECOMS Services
NEW YORK UNIVERSITY
212-992-7775