13 Replies Latest reply on Sep 3, 2013 5:54 PM by ANNEC

    materialized view showing red cross in front of it in SQL developer

    ANNEC

      we created a materialized view in oracle database,it works fine, and other views that select on  it return results and work fine too. But it has a red cross in front of it in oracle developer.

      I then refresh the view, or recreate the view, it shows green, but after a while when I come back, the red cross show up again.

      What does it mean, any error message we can find?

       

      Thanks

        • 1. Re: materialized view showing red cross in front of it in SQL developer
          Jim Smith

          It means the Materialized view is invalid and is just reflecting the data in the underlying dictionary tables.   Materialized views get flagged as invalid pretty frequently even though it continues to work properly.  Google will find lots of articles about this.

           

          It is nothing to worry about although it is annoying.

          • 2. Re: materialized view showing red cross in front of it in SQL developer
            ANNEC

            I would like to mark this correct answer, but not very clear about when you say, it is just reflecting the data in underlying dictionary tables, so it still reflects the data correctly of its underlying table?

            what I observed is if I change any data in the underlying table, the MV becomes invalid, I refresh it using the command

             

             

            exec DBMS_MVIEW.REFRESH('MYMV','c');

            , it becomes valid again.

            So I am not sure if we don't refresh it, will the materialized view still reflect the underlying table in sync or it is old data?

             

            Thanks much,

            • 4. Re: materialized view showing red cross in front of it in SQL developer
              Jim Smith

              The X means that the dictionary  table DBA_OBJECTS (or USER_OBJECTS) shows the MV is invalid. The content of the materialized view is correct.

              • 5. Re: materialized view showing red cross in front of it in SQL developer
                The content of the materialized view is correct.

                No - that is not necessarily true. Any DML on a dependent object will render the MV invalid. See my comments and the doc section in my reply to OP.

                • 6. Re: materialized view showing red cross in front of it in SQL developer

                  878566 wrote:

                   

                  we created a materialized view in oracle database,it works fine, and other views that select on  it return results and work fine too. But it has a red cross in front of it in oracle developer.

                  I then refresh the view, or recreate the view, it shows green, but after a while when I come back, the red cross show up again.

                  What does it mean, any error message we can find?

                  It means your system is working NORMALLY! There is no 'error message' to find because there is no error. What you describe is EXACTLY how MVs are designed to work.

                   

                  It is NORMAL for MVs to be, and to become, invalid, especially if there refresh setting is ON DEMAND. Don't confuse 'validity' with 'staleness'; they are very different things.

                   

                  See the section 'Invalidating Materialized Views' in the Data Warehousing Guide'

                  http://docs.oracle.com/cd/B28359_01/server.111/b28313/advmv.htm#i1007082

                  Invalidating Materialized Views

                  Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail 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. To revalidate a materialized view, use the ALTER MATERIALIZED VIEW COMPILE statement.

                  A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view did not have one of the query rewrite privileges and that privilege has now been granted to the owner, you should use the following statement to revalidate the materialized view:

                  ALTER MATERIALIZED VIEW mview_name COMPILE;  

                  The state of a materialized view can be checked by querying the data dictionary views USER_MVIEWS or ALL_MVIEWS. The column STALENESS will show one of the values FRESH, STALE, UNUSABLE, UNKNOWN, UNDEFINED, or NEEDS_COMPILE to indicate whether the materialized view can be used. The state is maintained automatically. However, if the staleness of a materialized view is marked as NEEDS_COMPILE, you could issue an ALTER MATERIALIZED VIEW ... COMPILE statement to validate the materialized view and get the correct staleness state. If the state of a materialized view is UNUSABLE, you must perform a complete refresh to bring the materialized view back to the FRESH state. If the materialized view is based on a prebuilt table that you never refresh, you will need to drop and re-create the materialized view.

                  As the doc states Oracle will normally 'revalidate' an MV when it is referenced. You can do this manually by using 'ALTER . . . COMPILE'. For an ON DEMAND MV compiling it will NOT alter the contents of the MV - it may stil be STALE.

                   

                  You don't need to REFRESH or recreate the MV to make it valid; just recompile it. If it doesn't become VALID after you compile it then there is usually an issue with one of the dependent objects that you need to resolve.

                  • 7. Re: materialized view showing red cross in front of it in SQL developer
                    Jim Smith

                    Well, yes and no.

                     

                    IMO the way the the DBMS handles this is wrong and confusing.

                     

                    DML on underlying tables will make the data in the MV out of date.  That ought to be STALE, not INVALID.  An out of date MV is a perfectly valid state - they have refresh intervals for a reason. 

                     

                    Classifying out of date MVs the same as broken procedures or views is misleading.

                    • 8. Re: materialized view showing red cross in front of it in SQL developer
                      IMO the way the the DBMS handles this is wrong and confusing.

                       

                      Wrong? I can't agree since that would be a functional issue and I'm not aware of any functional issues that result from the DBMS handling it that way.

                       

                      Confusing? Certainly can be.

                      DML on underlying tables will make the data in the MV out of date.  That ought to be STALE, not INVALID.

                      But it is. 'Status' and 'Staleness' are two DIFFERENT attributes. See the STALENESS column of USER_MVIEWS and it will be 'FRESH' when the MV data is in sync with the source data and will be 'NEEDS_COMPILE' when the source data changes.

                        An out of date MV is a perfectly valid state - they have refresh intervals for a reason. 

                      Classifying out of date MVs the same as broken procedures or views is misleading.

                      I agree it can be misleading IF you make assumptions about what VALID and INVALID mean. But even for procedures/funcitons/packages/views the common interpretation, in my experience, is that the object needs to be recompiled to be made valid. And for code and MVs Oracle will attempt to recompile/revalidate the object when it is referenced.

                       

                      And I also agree that an 'out of date MV' is perfectly usable and, especially for ON DEMAND types, perfectly normal. But I would not say that condition is a 'perfectly valid state' since we already know that when the MV first becomes 'out of date' Oracle will change the status to 'INVALID' ctedly valid for an MV

                       

                       

                      Only Oracle knows for sure and knows ALL of the many uses of that particular piece of data. The 'XXX_OBJECTS' views only provide a very sanitized version of a very selected set of data from the underlying data dictionary tables. In particular the STATUS column uses 'INVALID' for any value other than 0 or 1 but there are several other values it could really be.

                      DECODE (o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),

                      . . .

                           FROM   sys."_CURRENT_EDITION_OBJ" o, sys.user$ u


                      select distinct status from "_CURRENT_EDITION_OBJ"

                      STATUS

                      1

                      5

                      4

                      0

                      If you query the views for a sample MV named MV_EMP based on the SCOTT.EMP table:

                      select object_id, object_type, dba.status, ceo.status

                      from dba_objects dba,

                      "_CURRENT_EDITION_OBJ" ceo

                      where dba.object_id = ceo.obj#

                         and dba.owner = 'SCOTT' and dba.object_name like 'MV%'

                      If you query the data for a sample MV named MV_EMP:

                       

                      OBJECT_ID,OBJECT_TYPE,STATUS,STATUS_1

                      85942,MATERIALIZED VIEW,INVALID,5

                      85940,TABLE,VALID,1

                      The 'STATUS' of the MV (after a single INSERT/COMMIT) is '5' and that causes DBA_OBJECTS to list the status as INVALID. But Oracle knows the status as '5' and my presumption would be that their code uses that actual value and not the sanitized view value.

                       

                      I can't speak for Oracle but perhaps they didn't really see any value in providing any more granularity to the STATUS value provided in the system view. They already the STALENESS information in the XXX_MVIEWS system view.

                       

                      As with many other things we 'outsiders' can speculate, hope, wish and pray all we want but it won't change the current reality of the implemenation!

                      • 9. Re: materialized view showing red cross in front of it in SQL developer
                        ANNEC

                        Thanks all, I still feel a little confusing:

                        here is the statement of creating my MV;

                         

                        CREATE materialized VIEW prefsschoolyeardaily

                        build IMMEDIATE

                        AS SELECT DISTINCT name,

                          dbms_lob.Substr(value, 5, 1) AS Value,

                          schoolid,

                          yearid

                          FROM prefs

                          WHERE Lower(name) = 'att_recordmodedaily'

                          AND value LIKE '1%';

                         

                        CREATE INDEX idx_psyd_schoolid

                          ON prefsschoolyeardaily (schoolid);

                         

                        CREATE INDEX idx_psyd_schoolidyearid

                          ON prefsschoolyeardaily (schoolid, yearid);

                         

                        CREATE INDEX idx_psyd_yearid

                          ON prefsschoolyeardaily (yearid);

                         

                        GRANT SELECT ON prefsschoolyeardaily TO PUBLIC;

                         

                        I found any time DML happened on the underlying table, the MV shows a red cross next to it.

                        I did a query:Select * From  User_Mviews, I see in the refresh Mode it is ON Demand, the staleness column is need_compile, so I run the following command:

                        Alter Materialized View Prefsschoolyeardaily Compile, I see compile state is Valid, and staleness column becomes unknown,

                        If I do

                        exec DBMS_MVIEW.REFRESH('PREFSSCHOOLYEARDAILY','c'); the result is the same.

                         

                        What does this mean, shall I still ignore the red cross, and the table data is stale or not?

                        Thanks much

                        • 10. Re: materialized view showing red cross in front of it in SQL developer
                          Jim Smith

                          You can usually ignore the red cross.    If recompiling makes it go away the MV is not really "invalid" on the sense of broken.

                          The data will be "stale", but that is inevitable for most MVs - the data will always be stale between refreshes.

                          • 11. Re: materialized view showing red cross in front of it in SQL developer
                            ANNEC

                            Thanks,

                             

                            So as I understand now, I should ignore the red cross, since after refresh it will disappear.  But the data could mean stale.

                            for this MV, we used it for nightly extract to flat file to other system. So before generating the extract we run the command refresh, it makes the red cross disappear, and I think the data become freshed too, so we are good, correct?

                             

                            Thanks,

                            • 12. Re: materialized view showing red cross in front of it in SQL developer
                              Jim Smith

                              Yes.

                               

                              There is always a small risk that the MV will be come really invalid, as a result of DDL for example, and fail to refresh, but if you build the refresh into the extract procedure with appropriate error checking you should be OK.

                              • 13. Re: materialized view showing red cross in front of it in SQL developer
                                ANNEC

                                Thank you, Jim.

                                I thought this is OK too, but just noticed either after refresh or compile, the staleness column becomes unknown, I was expecting it becomes fresh.