My Oracle Support Banner

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

Last updated on MARCH 12, 2021

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

 

You receive the following error while creating Materialized  view that has has nested table and user type: ORA-32331: type "<mv_owner>"."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 "<user1>"."<master_table>"
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_<master_table>"
/

ALTER MATERIALIZED VIEW LOG ON "<user1>"."<master_table>" ADD (CONTACTS)
/

create materialized view log on "<user1>"."<master_table2>"
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='<user1>;

OWNER                          TYPE_OID
------------------------------ --------------------------------
<user1>                     059013500E197797E0530E02A8C0F26C

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

OWNER                          TYPE_OID
------------------------------ --------------------------------
<user1>                      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 <mview1>
NESTED TABLE STAKE_CONTACT_CONTACTS_TAB STORE AS MV_STAKE_CONTACT_CONTACTS_TAB
REFRESH FAST ON DEMAND
AS SELECT *
from <user1>.<table_name>@<> ;


Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.