Materialized view "invalidates" when adding 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 stuff. After creating this materialized view, it is valid and populated with the data I would expect. I then add a constraint to its table, like so:

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 good in the world.

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

ALTER MATERIALIZED VIEW mv_cis COMPILE;

No change. Oddly, refreshing the view still works, but it drives me crazy that it shows invalid, and makes my test plan fail even though everything looks to be in order. So, what am I missing?

Answer 1

I'm gonna say bug, and you should check Metalink and/or submit a SR.

Here is 11.2 scenario. Granted it is simplistic. 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 need compile.

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