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 and later
Information in this document applies to any platform.


Source Database Version :

Target Database Version  :

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


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

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