ORA-32331: Type "MVREP"."TAB_CONTACT" Is Incompatible With The Master Trying To Create Materialized View (Doc ID 1968550.1)

Last updated on APRIL 27, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

You receive the following error while creating Materialized  view that has has nested table and user type: ORA-32331: type "MVREP"."TAB_CONTACT" is incompatible with the master.

This is a Distributed Materialized View.

Steps to reproduce:


On Master site:

create table (ddl not included)

CREATE MATERIALIZED VIEW LOG
ON "PRODUCTION"."STAKEHOLDER_CONTACT"
PCTFREE 60 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MLOG_STAKEHOLDER_CONTACT"
/

ALTER MATERIALIZED VIEW LOG ON "PRODUCTION"."STAKEHOLDER_CONTACT" ADD (CONTACTS)
/

create materialized view log on production.stake_contact_contacts_tab
tablespace MLOG_STAKE_CONTACT_CONTACTS_TB
/

Here are the two types from Master Site:
SQL> select owner, type_oid from dba_types where type_name='TAB_CONTACT' and owner='PRODUCTION';

OWNER                          TYPE_OID
------------------------------ --------------------------------
PRODUCTION                     059013500E197797E0530E02A8C0F26C

SQL> select owner, type_oid from dba_types where type_name='ROW_CONTACT' and owner='PRODUCTION';

OWNER                          TYPE_OID
------------------------------ --------------------------------
PRODUCTION                     059013500DB67797E0530E02A8C0F26C

ON Mview Site:

CREATE OR REPLACE TYPE ROW_CONTACT oid '059013500DB67797E0530E02A8C0F26C' AS OBJECT (
CONTACT_ID NUMBER(16, 0),
CONTACT_TYPE VARCHAR2(50),
CONTACT_VALUE VARCHAR2(255),
CONTACT_EXTENSION VARCHAR2(10),
CONTACT_COMMENT VARCHAR2(100),
CONTACT_DESCRIPTION VARCHAR2(100),
CONTACT_SEQUENCE NUMBER(16, 0)
);

CREATE OR REPLACE TYPE TAB_CONTACT oid '059013500E197797E0530E02A8C0F26C' AS
  TABLE OF ROW_CONTACT;

CREATE MATERIALIZED VIEW mv_stakeholder_contact
NESTED TABLE STAKE_CONTACT_CONTACTS_TAB STORE AS MV_STAKE_CONTACT_CONTACTS_TAB
REFRESH FAST ON DEMAND
AS SELECT *
from production.stakeholder_contact@<<dblink>> ;


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