Materialized View has COMPILE_STATE of COMPILATION_ERROR After Compile is Run (Doc ID 1109197.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.6 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Symptoms

The Version where the Master table is located is 9.2.0.6 in this customer's case.

The Version where the MVIEW is being created is 10.2.0.4 in this case.

SQL> select mview_name, compile_state from dba_mviews where owner='SCOTT' and mview_name like 'ODBTEST1';

no rows selected

SQL> conn SCOTT/pwd
Connected.

SQL> CREATE MATERIALIZED VIEW SCOTT.ODBTEST1
TABLESPACE ODB_TABLE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT * FROM scott.emp@WODB_DB_LNK;

Materialized view created.

SQL> select mview_name, compile_state from user_mviews where owner='SCOTT' and mview_name like 'ODBTEST1';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 VALID

SQL> execute dbms_mview.refresh('SCOTT.ODBTEST1');

PL/SQL procedure successfully completed.

SQL> select mview_name, compile_state from user_mviews where owner='SCOTT' and mview_name like 'ODBTEST1';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 VALID

SQL> alter materialized view ODBtest1 compile;

Materialized view altered.

SQL> select mview_name, compile_state from user_mviews where owner='SCOTT' and mview_name like 'ODBTEST1%';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 COMPILATION_ERROR

Changes

Tested between 10.2.0.4 and 10.2.0.4 and the mview does not go INVALID or into COMPILATION_ERROR.

CREATE MATERIALIZED VIEW ODBTEST1
TABLESPACE users
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT IDACNT, DS FROM rk_mview.ODB$ACN_ACNMF@S102W6.loopback ODB$ACN_ACNMF ;

Materialized view created.

SQL> select mview_name, compile_state from user_mviews where mview_name like 'ODBTEST1%';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 VALID

SQL> execute dbms_mview.refresh('ODBTEST1');

PL/SQL procedure successfully completed.

SQL> select mview_name, compile_state from user_mviews where mview_name like 'ODBTEST1%';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 VALID

SQL> execute dbms_mview.refresh('ODBTEST1');

PL/SQL procedure successfully completed.

SQL> select mview_name, compile_state from user_mviews where mview_name like 'ODBTEST1%';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 VALID

SQL> alter materialized view ODBtest1 compile;

Materialized view altered.

SQL> select mview_name, compile_state from user_mviews where mview_name like 'ODBTEST1%';

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
ODBTEST1 VALID


So, this does not reproduce in 10.2.0.4.

So, this looks like some issue between 10.2 and 9.2.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms