ORA-12060 Creating MView on Prebuilt Table with Char Vs Byte Semantics (Doc ID 1301166.1)

Last updated on FEBRUARY 27, 2013

Applies to:

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

Symptoms

Master database running WE8ISO8859P1.
Mview Site running AL32UTF8.

Trying to use prebuilt table option to create their materialized view.

On Master table defined as:

CREATE TABLE EDU_AFFIL
(
AFFILIATION_ID NUMBER NOT NULL,
REGISTRATION_ID NUMBER NOT NULL,
SCHOOL_ID NUMBER NOT NULL,
SCHOOL_ROLE_ID NUMBER NOT NULL,
FIRST_NAME VARCHAR2(30 BYTE) NOT NULL,
LAST_NAME VARCHAR2(30 BYTE) NOT NULL,
YEAR NUMBER,
START_YEAR NUMBER NOT NULL,
END_YEAR NUMBER NOT NULL,
SALES_PROGRAM_ID NUMBER NOT NULL,
VERSION NUMBER DEFAULT 0 NOT NULL,
VISIBLE_FLAG NUMBER,
TYPE NUMBER NOT NULL,
REGISTRANT_SCHOOL_ID NUMBER,
CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER,
CREATED_BY_PROCESS NUMBER,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_PROCESS NUMBER,
BIO_ICON_DATE DATE,
ANNOUNCEMENT_ICON_DATE DATE,
PHOTO_ICON_DATE DATE,
URL_ICON_DATE DATE,
DISPLAY_STATE INTEGER NOT NULL,
DISPLAY_STATE_LAST_UPDATED DATE NOT NULL,
USER_ICON_BITS NUMBER(20),
AFFIL_ICON_BITS NUMBER(20),
EVENT_INTEREST_ICON_DATE DATE,
SUPPLEMENTAL LOG GROUP ED_AFFILIATIONS
(REGISTRATION_ID,SCHOOL_ID,SCHOOL_ROLE_ID,FIRST_NAME,LAST_NAME,YEAR,START_YEAR
,END_YEAR,TYPE,CREATION_DATE,DISPLAY_STATE,DISPLAY_STATE_LAST_UPDATED,USER_ICO
N_BITS,AFFIL_ICON_BITS,LAST_UPDATE_DATE,VERSION)
)
TABLESPACE users
PARTITION BY HASH (REGISTRATION_ID)
PARTITIONS 32
STORE IN
(users,users,users,users,users,users,users,users,users,users,users,users,users
,users,users,users,users,users,users,users,users,users,users,users,users,users
,users,users,users,users,users,users)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

On Mview Site, table columns changed:

FIRST_NAME VARCHAR2(30 CHAR) NOT NULL,
LAST_NAME VARCHAR2(30 CHAR) NOT NULL,


The rest of the table is identical.

Materialized view statement fails with:

CREATE SNAPSHOT EDU_AFFIL ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FAST START WITH SYSDATE NEXT sysdate+5/(24*60)
AS SELECT * FROM EDU_AFFIL@ST2W.STG2.CMATES.COM;


AS SELECT * FROM EDU_AFFIL@ST2W.STG2.CMATES.COM
*
ERROR at line 3:
ORA-12060: shape of prebuilt table does not match definition query


The materialized view is created fine when the following column definitions are used:

FIRST_NAME VARCHAR2(90 BYTE) NOT NULL,
LAST_NAME VARCHAR2(90 BYTE) NOT NULL,

Changes

Trying to change the semantics of the column due to the change from single byte to multi-byte character set. Without the change data would be truncated.

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