删除表空间报错ORA-02429

报错现象：

drop tablespace users including contents and datafiles;
报错：
ORA-02429: cannot drop index used for enforcement of unlque/primary key

处理办法：

1.先尝试：

drop tablespace users including contents and datafiles cascade constraints;

2.1不行，则继续查u/p：

根据sql查看需要删除的约束：
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 = 'USERS');
然后将脚本提取执行删除操作

2.2还有可能报错：

SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
第 1 行出现错误:
ORA-22868: 具有 LOB 的表包含有位于不同表空间的段

检查这个表空间上的Lob对象:
SQL> select owner, table_name, column_name, tablespace_name from dba_lobs where tablespace_name = 'USERS';
删除表后可继续。

2.3彻底一点，直接删表就行了：
select 'drop table '||owner||'.'||table_name||' purge;' from dba_tables where tablespace_name in('USERS');

相关参考：

Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (文档 ID 1918060.1)