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
转载请注明出处