5

I created some materialized views and Oracle SQL Developer puts a little red 'x' next to each of them. At the moment they are returning the correct information when I query them and running the following query in SQL Plus suggests that there are no errors:

SELECT * FROM USER_SNAPSHOTS

The ERROR column in this returns 0 for the materialized views in question.

Does anyone know why SQL Developer thinks there is an error? Is there anywhere else I can check?

UPDATE

Taking Patrick's advice I ran the following query:

SELECT * FROM ALL_MVIEWS

The COMPILE_STATE is 'NEEDS_COMPILE' for each view in question. What does this mean? Why would it need to be recompiled? None of the underlying tables have been changed.

user1578653
  • 4,888
  • 16
  • 46
  • 74
  • I would confirm the compile_state column of the materialized views you are referring to with the view, all_mviews. – Patrick Bacon Sep 08 '14 at 14:37
  • Unexplained non-thrown errors during compilation of the materialized views? If you created these objects in the current session, you could query the view, user_errors. – Patrick Bacon Sep 08 '14 at 15:03

3 Answers3

4

To fix 'red' cross icon on views (actually it is a white cross over red background) due to NEEDS_COMPILE run the ALTER VIEW COMMAND.

ALTER VIEW MY_VIEW COMPILE;

Check ORACLE SQL Reference about ALTER VIEW.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4004.htm

gabriel capparelli
  • 91
  • 4
  • The question was about materialized view, but thanks for hint, it worked for me - `ALTER materialized VIEW MY_VIEW COMPILE;` – Betlista Oct 21 '19 at 10:42
2

For some reason, simply refreshing the materialized views made the 'error' go away. So not a true error, more of a reminder that the data isn't up to date. I guess you can ignore it if the table structure hasn't actually changed then...

user1578653
  • 4,888
  • 16
  • 46
  • 74
0

This can be caused by modifications to an underlying table that the materialized view is based on. For example: increasing the max size of a column in the table which is included in the materialized view.

To refresh the materialized view you can do the following:

BEGIN
DBMS_SNAPSHOT.REFRESH('Name of materialized view');
END;
Edd
  • 8,402
  • 14
  • 47
  • 73