Materialized view is "invalid" when adding a constraint

Question

I have a materialized view in an Oracle 10.2.0.50 database that looks like this:

CREATE MATERIALIZED VIEW mv_cis NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT component_id, ctn, visible_tag, facility, SYSTEM, elev, parent, room_number, remarks, safety_class, seismic, quality_level FROM v_cis; 

Pretty straight forward. After creating this materialized view, it is valid and filled with data that I would expect. Then I add a constraint to my table, for example:

 ALTER TABLE mv_cis MODIFY ctn CONSTRAINT chk_cis_ctn_null NOT NULL ENABLE VALIDATE; 

This works as expected, the table gets a new constraint, and all is well in the world.

However, I then look back at the materialized view and (using Toad for Oracle 12), it shows an invalid. The user_mviews table shows that COMPILE_STATE of which NEED_COMPILE and STALENESS is UNDEFINED . So, I run:

 ALTER MATERIALIZED VIEW mv_cis COMPILE; 

Without changes. Oddly enough, updating the review still works, but it drives me crazy that it shows invalid and makes my test plan unsuccessful, although everything looks fine. So what am I missing?

Answer 1

I'm going to say a mistake, and you should check Metalink and / or send SR.

Here is scenario 11.2. Of course, it is simplified. I can try it with your actual DDL if you prefer.

 SQL> create table base(id integer primary key, name varchar2(100) not null, 2 description varchar2(400)); Table created. SQL> desc base Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) NAME NOT NULL VARCHAR2(100) DESCRIPTION VARCHAR2(400) SQL> create materialized view mv_base build immediate refresh force on demand 2 with primary key as 3 select id, name, description from base 4 ; Materialized view created. SQL> desc mv_base Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) NAME NOT NULL VARCHAR2(100) DESCRIPTION VARCHAR2(400) SQL> alter table mv_base modify description constraint chk_not_null not null 2 enable validate; Table altered. SQL> desc mv_base Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) NAME NOT NULL VARCHAR2(100) DESCRIPTION NOT NULL VARCHAR2(400) 

Now check if compilation is needed.

 SQL> select mview_name, compile_state, staleness from user_mviews; MVIEW_NAME COMPILE_STATE STALENESS ------------------------------ ------------------- ------------------- MV_BASE NEEDS_COMPILE NEEDS_COMPILE SQL> alter materialized view mv_base compile; Materialized view altered. SQL> select mview_name, compile_state, staleness from user_mviews; MVIEW_NAME COMPILE_STATE STALENESS ------------------------------ ------------------- ------------------- MV_BASE VALID FRESH