今日在帮助网友进行物化视图创建时,发生如下问题:
1 物化视图能够成功删除,但是在重新创建物化视图时报错如下报错ora-955 对象已经存在,查询user_objects发现对象确实存在,且状态为invalid状态。
问题解决:
根据相关内容查询metalink发现如下问题:
影响版本:
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***
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:
ORA04020: 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:
ORA00955: name is already used by an existing object
---创建特殊的domain索引,出现上述问题,2 解决方案
讲domain索引删除后,重新删除物化视图,创建物化视图成功。
Drop special (domain) index on MV container table before dropping the MV.