ORA-02429: cannot drop index used for enforcement of unique /primary key

ORA-02429: cannot drop index used for enforcement of unique /primary key
这个错误,对应的中文提示“ORA-02429: 无法删除用于强制唯一/主键的索引”

oerr ORA 02429
02429, 00000, "cannot drop index used for enforcement of unique/primary key"
// *Cause: user attempted to drop an index that is being used as the
// enforcement mechanism for unique or primary key.
// *Action: drop the constraint instead of the index.

ORA-02429错误的原因是因为用户试图删除一个用于强制唯一/主键的索引,解决方法也很简单，删除对应的约束就会自动删除该索引。

1：新建测试表TAB_TEST， 如下所示：
CREATE TABLE TAB_TEST
(
JOB_ORDER_NO VARCHAR2(20 BYTE),
DIMM_ID NUMBER,
MRP_GROUP_CD VARCHAR2(10 BYTE),
ITEM_CAT VARCHAR2(20 BYTE),
REQUIRED_DATE DATE,
PURCHASED_BY VARCHAR2(10 BYTE),
USED_BY VARCHAR2(10 BYTE),
SUPPLIER_CD VARCHAR2(10 BYTE)
);

2：添加主键约束，如下所示
ALTER TABLE TAB_TEST ADD CONSTRAINT PK_TAB_TEST PRIMARY KEY(JOB_ORDER_NO, DIMM_ID, MRP_GROUP_CD, ITEM_CAT);

3：查看测试表的索引信息
SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='TAB_TEST';

4：查看测试表的约束信息：
SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='TAB_TEST';

5:删除测试表的索引PK_TAB_TEST
DROP INDEX PK_TAB_TEST;

DROP INDEX PK_TAB_TEST
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

解决方法:
删除对应的约束就会自动删除该索引。而不是直接去删除该索引。
ALTER TABLE TAB_TEST DROP CONSTRAINT PK_TAB_TEST;
SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='CONSTRAINT_NAME';
SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='TAB_TEST';

-------------------
select name from v$tablespace;
select name from v$datafile;
create tablespace test1 datafile '/oradata/DB02/test01.dbf' size 10m;
select * from dba_tables t where t.table_name='T1';
drop table t1 purge;
create table t1 as select * from dba_objects where rownum<9;
select * from t1;
alter table t1 add constraint pk_id primary key (object_id) using index tablespace test1;
select * from dba_indexes i where i.table_name ='T1';
select * from dba_constraints c where c.TABLE_NAME='T1';


drop tablespace test1 including contents and datafiles;
--ORA-00604: error occurred at recursive SQL level 1
--ORA-02429: cannot drop index used for enforcement of unique/primary key

--解决方法:
select e.owner,segment_name,e.segment_type,partition_name,tablespace_name from dba_extents e where tablespace_name=upper('test1');

select 'alter table ' || owner || '.' || table_name || ' drop constraint ' || constraint_name || ';'
from dba_constraints
where constraint_type in ('U', 'P')
and (index_owner, index_name) in
(select owner, segment_name
from dba_segments
where tablespace_name = upper('test1'));

drop tablespace test1 including contents and datafiles;