Create Materialized View Results in ORA-955 But Drop Materialized View Does Not Succeed Either (Doc ID 1358255.1)

Last updated on NOVEMBER 08, 2024

Applies to:

Symptoms

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.

Changes

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.

Cause

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