2 Replies Latest reply: May 28, 2014 3:04 AM by B.Delmée RSS

    status icons are not always consistent between navigation tree and schema browser (at least for MV's)

    B.Delmée

      We got concerned about the status of some of our materialized views because they show up as red icons in the SqlDev navigation tree

      (so do they in toad, FWIW). Apparently many people have been wondering about this, and the opinion as to whether there is cause to worry or not

      seems to have changed depending on DB version and who you asked (e.g USER_SNAPHSOTS.STATUS vs USER_MVIEWS.STALENESS)

      Not SQL-developer specific I know,but if anyone has sorted it out I'd be grateful to hear...

       

      What *is* SqlDev-specific, though, is that if I browse my MV's through the schema browser, there they show as green icons.

      Can someone clarify whether the schema browser simply does not bother reflecting status at all or if it uses different/more sensible criteria?

        • 1. Re: status icons are not always consistent between navigation tree and schema browser (at least for MV's)
          Gary Graham-Oracle

          The answer may depend both on the version of the database and the version of SQL Developer you are running.  On 4.0.2, viewing MV's from the navigator tree or the schema browser, the following SQL runs if the DB version is 9 or higher:

          select * from ( select mview_name name, '' short_name,

                                          :SCHEMA OBJECT_OWNER,

                                          mview_name OBJECT_NAME,

                                          case when COMPILE_STATE='VALID' then 'FALSE' else 'TRUE' end INVALID

              from sys.all_mviews

              where owner = :SCHEMA

                  union all

              select mview_name name, syn.SYNONYM_NAME short_NAME,

                                          syn.TABLE_OWNER OBJECT_OWNER,

                                          syn.TABLE_NAME OBJECT_NAME,

                                          case when COMPILE_STATE='VALID' then 'FALSE' else 'TRUE' end INVALID

              from sys.all_mviews s,sys.user_synonyms syn

              where syn.table_owner = s.owner

              and    syn.TABLE_NAME = s.mview_name

              and :INCLUDE_SYNS = 1

          )

          If your environment differs, you can try capturing the SQL in the following ways:

          1. Launch SQL Developer after replacing the IncludeConfFile  sqldeveloper-nondebug.conf in your sqldeveloper.conf file with sqldeveloper-debug.conf, and View > Log

          2. Or use Tools > Monitor Sessions to enable tracing on the session of interest (right-click Trace Session). Get the resulting .trc file, then tkprof it or drag & drop it into a worksheet.

           

          Hope this helps,

          Gary

          SQL Developer Team

          • 2. Re: status icons are not always consistent between navigation tree and schema browser (at least for MV's)
            B.Delmée

            Hello Gary and thanks for looking into this. I am running SqlDev 4.02 against an 11r2 db.

             

            So you're saying that the same statement is used to get the list of MV's for both navigator and browser and I get identical lists indeed.

            What I am seeing though, is that MV's with a COMPILE_STATE of NEEDS_RECOMPILE are displayed in red in the navigator,

            and in green in the browser. Your answer seems to imply you are looking at COMPILE_STATE as the main validity flag,

            which is already an indication as it could have been USER_MVIEWS.STALENESS, USER_MVIEWS.COMPILE_STATE,

            USER_SNAPSHOTS.STATUS or  USER_OBJECT.STATUS....

             

            I have cases of an MV with a COMPILE_STATE=VALID yet STALENESS=UNUSABLE, they show up as green currently;

            not sure this is the best representation or for that matter when one needs worry and be alerted about their MV's state.