Create Materialized View Results in ORA-955 But Drop Materialized View Does Not Succeed Either
Last updated on FEBRUARY 27, 2013
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 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.
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