oracle强制清空表,oracle 数据库强制删除带主键的表空间

问题如下：

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，改为你的表空间名称即可，其他的更加你的返回值做相应的小改动即可