ora-02429排查

ora-02429:无法删除用于强制唯一/主键的索引

由于将主键约束建立到所要删除的表空间中，所有导致删除这个表空间报如上错误

解决方法：

ql>select segment_name,partition_name,tablespace_name from dba_extents where tablespace_name=upper('表空间名'); //查得主键约束

由

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 = '表空间名'); //生成删除主键约束的语句

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 = '  2    3    4    5    6    7  aaa');

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter table aaa.bbb drop constraint PK_ST_BAIDU_REPORT ;   //生成的删除语句

SQL> alter table aaa.bbb drop constraint PK_ST_BAIDU_REPORT ; //手动执行

Table altered.

然后删除表空间

SQL> drop tablespace SEMTOOLS;
drop tablespace SEMTOOLS
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SQL> drop tablespace SEMTOOLS INCLUDING CONTENTS;

Tablespace dropped. //删除OK

END

本文属笔者原创，脚本引自“http://blog.csdn.net/yudehui/article/details/8098936”

作者：john

转载请注明出处