Create Materialized View Results in ORA-955 But Drop Materialized View Does Not Succeed Either
(Doc ID 1358255.1)
Last updated on MARCH 28, 2019
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Trying to create a Materialized View (MView, MV) results in an ORA-955 error:
However, dropping the Materialized View seems to work (without error) or generated errors such as:
The situation is prevents deletion of the Materialized View and re-creating the Materialized View is also not possible.
You may have created a spatial index or CONTEXT/CTXCAT index on the Materialized View after it was created, but attempted to drop the MView before that index had been dropped. Once the DROP MVIEW command fails, dropping the index does not correct the issue.
You may have defined the Materialized View on a PREBUILT TABLE (see the documentation for a detailed explanation). In this case, dropping the Materialized View will preserve the container table as a regular table object. This prevents a CREATE MVIEW command from executing successfully. You would either drop that table first or use the ON PREBUILT TABLE clause. Oracle will then assume that the data in the container table is accurate without validation.
There may be other changes that lead to the same issue described in this note.
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