My Oracle Support Banner

Materialized View Creation on Prebuilt Table Fails with ORA-12060 (Doc ID 1379574.1)

Last updated on MARCH 10, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
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 Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

SQL>create table
XYZ (
ROUTE_CD number);

SQL> create table ABCD
AS
SELECT XXX
FROM
( SELECT XXX1 AS XXX
FROM XYZ
) RCSM;

SQL> CREATE MATERIALIZED VIEW ABCD
--BUILD DEFERRED
ON PREBUILT TABLE WITH REDUCED PRECISION
AS
SELECT XXX
FROM
( SELECT XXX1 AS XXX
FROM XYZ
) XYZ01;


ERROR at line 5: ORA-12060: shape of prebuilt table does not match definition query



No such problem(error) if the column wasn't aliased.

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.