Create Materialized View Results in ORA-955 But Drop Materialized View Does Not Succeed Either

(Doc ID 1358255.1)

Last updated on FEBRUARY 27, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

Trying to create a Materialized View (MView, MV) results in an ORA-955 error:

ORA-00955 <name> is already used by an existing object

 However, dropping the Materialized View seems to work (without error) or generated errors such as:

ORA-04020: deadlock detected while trying to lock object <schema>.<object name>

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

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