My Oracle Support Banner

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

Last updated on APRIL 18, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 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:

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.


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

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