CREATE MVIEW WITH PREBUILT TABLE OPTION ON IOT TABLE ERROR ORA-12082 IN 11.2.0.2 (Doc ID 1292458.1)

Last updated on FEBRUARY 07, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.
11.2.0.2

Symptoms

CREATE MVIEW with ON PREBUILT TABLE option on IOT TABLE fails with ORA-12082

The same script works in 10.2.0.5 and 11.2.0.1.
Not working in 11.2.0.2

Testcase:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL v11202> CREATE TABLE Test_Table (Cd VARCHAR2(50), PRIMARY KEY (Cd))
/ 2

Table created.

SQL v11202> CREATE TABLE Test_IOT (Cd VARCHAR2(50), "COUNT(*)" NUMBER,
PRIMARY KEY (Cd)) ORGANIZATION INDEX
/ 2

Table created.

SQL v11202> CREATE MATERIALIZED VIEW Test_IOT ON PREBUILT TABLE AS
SELECT Cd, COUNT(*) FROM Test_Table GROUP BY Cd
/ 2 3
CREATE MATERIALIZED VIEW Test_IOT ON PREBUILT TABLE AS
*
ERROR at line 1:
ORA-12082: "MURU"."TEST_IOT" cannot be index organized

Changes

The same script works in 10g and 11.2.0..
This is not working in 11.2.0.2

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