操作系统:REDHAT 5.5
数据库版本:11.2.0.3+asm+rac
问题描述:使用drop index historysettlement.PK_TEST 删除一索引时,出现ora-02429: 无法用于删除强制唯一/主键的索引错误.
SQL> drop index historysettlement.pk_test;
drop index historysettlement.pk_test
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>
1.1.1 检查索引信息
SQL> select t.table_name, t.index_type,t.uniqueness,t.status from dba_indexes t
2 where t.index_name='PK_TEST'
3 and t.owner='HISTORYSETTLEMENT';
TABLE_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
TEST NORMAL UNIQUE VALID
SQL>
1.1.2 原因
// *Cause: user attempted to drop an index that is being used as the
// enforcement mechanism for unique or primary key.
用户在使用drop命令删除主键或者唯一键索引时,会出现此错误.
1.1.3 解决方法
使用alter table tablename drop constraint cons_name 命令来代替drop index 命令.
SQL> alter table historysettlement.test drop constraint pk_test;
Table altered.
SQL>