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

Question

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.

Laryngo

I would confirm the compile_state column of the materialized views you are referring to with the view, all_mviews. – Brubaker

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. – Brubaker

Answer 1

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...

Laryngo

Answer 2

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

Carduaceous

The question was about materialized view, but thanks for hint, it worked for me - ALTER materialized VIEW MY_VIEW COMPILE; – Kalvin

Answer 3

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;

Infrangible