My Oracle Support Banner

Drop Materialized View with CONTEXT/CTXCAT Index on it Fails with ORA-4020 and getting ORA-955 while Recreating MVIEW (Doc ID 1308991.1)

Last updated on MARCH 04, 2013

Applies to:

Oracle Text - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Feb-2013***

Symptoms

ORA-4020, a self-deadlock, is reported when dropping a MVIEW with a Text (context or ctxcat) index and objects exists error, ORA-955, is thrown while recreating the MVIEW.

SQL> create materialized view VM_BO_PRICING refresh complete on demand as select * from user_objects;

Materialized view created.

SQL> create index X_VM_BO_PRICING_1 on VM_BO_PRICING (object_name) indextype is CTXSYS.CTXCAT;

Index created.

SQL> drop materialized view VM_BO_PRICING;
drop materialized view VM_BO_PRICING
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object MV.VM_BO_PRICING

SQL> drop materialized view VM_BO_PRICING;

Materialized view dropped.

SQL> create materialized view VM_BO_PRICING refresh complete on demand as select * from user_objects;
create materialized view VM_BO_PRICING refresh complete on demand as select * from user_objects
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Cause

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
Symptoms
Cause
Solution
 Workaround:
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.