问题如下：

SQL> drop tablespace TABLESPACENAME; 

drop tablespace TABLESPACENAME

*

ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

###########################


SQL> drop tablespace TABLESPACENAME INCLUDING CONTENTS and datafiles; 

drop tablespace TABLESPACENAME INCLUDING CONTENTS and datafiles

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

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

###########################

SQL> drop tablespace TABLESPACENAME including contents cascade constraints; 

drop tablespace TABLESPACENAME including contents cascade constraints

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

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


##########################################################

##########################################################

##########################################################


故障解决：

#############################

SQL> select segment_name,partition_name,tablespace_name from   dba_extents    where tablespace_name=upper('TABLESPACENAME');


SEGMENT_NAME

--------------------------------------------------------------------------------

PARTITION_NAME                 TABLESPACE_NAME

------------------------------ ------------------------------

SYS_C007455

                               TABLESPACENAME


SYS_C007455

                               TABLESPACENAME

.....

.....

.....

出现一大堆这样的列表

#############################

SQL> select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||' ;' 

  2  from dba_constraints 

  3  where constraint_type in ('U', 'P') 

   and (index_owner, index_name) in 

       (select owner, segment_name 

  4    5    6            from dba_segments 

         where tablespace_name = upper('TABLESPACENAME'));  7  


'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'

--------------------------------------------------------------------------------

alter table TABLESPACENAME.PUBLISH_QUEUE drop constraint SYS_C007455 ;

alter table TABLESPACENAME.OPER_LOG drop constraint SYS_C007446 ;

alter table TABLESPACENAME.NODE_HISTORY drop constraint SYS_C007421 ;

看到有上面三条alter命令后。执行者三条命令。有些数据量会显示更多alter命令。有的可能一两条，具体有多少条就执行多少次

##########################################################

SQL> alter table TABLESPACENAME.PUBLISH_QUEUE drop constraint SYS_C007455 ;


Table altered.


SQL> alter table TABLESPACENAME.OPER_LOG drop constraint SYS_C007446 ;


Table altered.


SQL> alter table TABLESPACENAME.NODE_HISTORY drop constraint SYS_C007421 ;


Table altered.

#########################################

之后再删除即可

SQL> drop tablespace TABLESPACENAME including contents cascade constraints; 


Tablespace dropped.

##################################


命令总结


drop tablespace TABLESPACENAME INCLUDING CONTENTS;

drop tablespace TABLESPACENAME; 

drop tablespace TABLESPACENAME INCLUDING CONTENTS and datafiles; 

drop tablespace TABLESPACENAME including contents cascade constraints; 

select segment_name,partition_name,tablespace_name from   dba_extents    where tablespace_name=upper('TABLESPACENAME');


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('TABLESPACENAME'));

 

alter table TABLESPACENAME.PUBLISH_QUEUE drop constraint SYS_C007455 ;

alter table TABLESPACENAME.OPER_LOG drop constraint SYS_C007446 ;

alter table TABLESPACENAME.NODE_HISTORY drop constraint SYS_C007421 ;


drop tablespace TABLESPACENAME including contents cascade constraints; 



注意：上面命令中只需要把表空间名称TABLESPACENAME，改为你的表空间名称即可，其他的更加你的返回值做相应的小改动即可