Why does SQL Developer think there's an error in my materialized views?

user1578653 picture user1578653 · Sep 8, 2014 · Viewed 16.8k times · Source

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.

Answer

gabriel capparelli picture gabriel capparelli · Aug 28, 2015

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