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:
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
The status in USER_MVIEWS show COMPILATION_ERROR - how can I find why this is INVALID??
I've also kicked of a refresh that seems to be working and hasn't complained the MV is INVALID??