• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

How to View Errors for Materialized Views in Oracle SQL Developer

Dear Sir / Madam

How to View Errors for Materialized Views in Oracle SQL Developer? I have some Materialized Views in an invalid state and I find not the reason.

Oracle Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


Sincerely

Ivo Raemy
0
ivoraemy
Asked:
ivoraemy
2 Solutions
 
Abhimanyu SuriSr Database EngineerCommented:
@?/rdbms/admin/utlxmv.sql -- Will create default MVIEW capability table

EXEC dbms_mview.explain_mview('SCHEMA.MVIEW'); -- Explain mview
There are other arguments to it as well

 select * from sys.mv_capabilities_table; -- see the report post explanation


Reference : https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_mview.htm#CEGGEHHC

Some other SQLs that may help

On Source

SELECT b.master,
       a.owner,
       a.name,
       A.SNAPSHOT_SITE,
       to_char(B.CURRENT_SNAPSHOTS,'yyyymmdd hh24miss')
  FROM DBA_REGISTERED_SNAPSHOTS a, DBA_SNAPSHOT_LOGS b
 WHERE A.SNAPSHOT_ID = B.SNAPSHOT_ID;


On target

SELECT owner,
       mview_name,
       master_link,
       refresh_mode,
       refresh_method,
       last_refresh_date
  FROM DBA_MVIEWS
0
 
Mark GeerlingsDatabase AdministratorCommented:
Often a status of "Invalid" for materialized views simply means that a base table the view is built on has been updated and the view has not been refreshed.  In my opinion, Oracle should not report these views as "Invalid" but rather something like "Stale" or "Needs Refresh".

So no, you may not be able to easily find a reason for why these views are "Invalid" other than the fact that some data has changed in the underlying table(s) for the materialized view.
1

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now