I've got a bit of a strange one here (8.1.7.4 on UNIX) - A MV that is INVALID when I query USER_OBJECTS but when I compile it via SQL PLUS it says it compiles but still remains INVALID:
The status in USER_MVIEWS show COMPILATION_ERROR - how can I find why this is INVALID??Code:SQL> select count(*) from MV_MOVEMENT_PROCESSES 2 / COUNT(*) ---------- 2050895 SQL> select status, object_type from user_objects where object_name='MV_MOVEMENT_PROCESSES'; STATUS OBJECT_TYPE ------- ------------------ VALID TABLE INVALID MATERIALIZED VIEW SQL> alter MATERIALIZED VIEW MV_MOVEMENT_PROCESSES compile; Materialized view altered. SQL> select status, object_type from user_objects where object_name='MV_MOVEMENT_PROCESSES'; STATUS OBJECT_TYPE ------- ------------------ VALID TABLE INVALID MATERIALIZED VIEW
I've also kicked of a refresh that seems to be working and hasn't complained the MV is INVALID??