?
Solved

How to View Errors for Materialized Views in Oracle SQL Developer

Posted on 2016-08-09
2
Medium Priority
?
230 Views
Last Modified: 2016-08-15
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
Comment
Question by:ivoraemy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
Abhimanyu Suri earned 1000 total points
ID: 41748915
@?/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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1000 total points
ID: 41752988
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question