My Oracle Support Banner

ORA-32304 error while creating A Materialized View With A Column Type MDSYS.SDO_GEOMETRY (Doc ID 2202121.1)

Last updated on AUGUST 04, 2018

Applies to:

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

Symptoms

Source Database Version : 12.1.0.2

Target Database Version  : 12.1.0.2

1. Create table on source database

create table <table name>
(
id NUMBER(20) not null,
country_id CHAR(3) not null,
countrysubdiv_id VARCHAR2(3),
second$countrysubdiv_id VARCHAR2(3),
geonamesfeatureclass_id CHAR(1),
geonamesfeaturecode_id VARCHAR2(10),
timezone_id NUMBER(20),
name VARCHAR2(200) not null,
datemodified DATE not null,
geometry MDSYS.SDO_GEOMETRY,
nameofficial VARCHAR2(200 CHAR) not null,
elevation NUMBER(20),
population NUMBER(38),
comments VARCHAR2(1024)
)
tablespace <tablespace name>
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);

2. On target database

create table <table name>
tablespace <tablespacec name> pctfree 5
storage (initial 128m next 128m)
logging parallel
as select a.*
from <table name>@<dblink> a;

alter table <table name> add constraint <constraint name> primary key (ID)
using index tablespace <tablespace name> pctfree 5 storage (initial 128k next 128k);

3. On target database it was throwing error while executing create mview syntax

create materialized view <mview name>
on prebuilt table
refresh fast on demand
with primary key
as
select * from <table name>@<dblink>;

While executing above syntax it was generated ora-32304 error as below

ORA-32304: materialized views with user-defined types cannot use prebuilt table

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
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.