删除未知的约束

 

SQL> select segment_name,owner,segment_type from dba_segments where tablespace_name='XIAO';

SEGMENT_NA OWNER                          SEGMENT_TYPE
---------- ------------------------------ ------------------
DB                 XXX                            TABLE
DB_P            XXX                            INDEX
DB_U1          XXX                            INDEX
DBINC           XXX                            TABLE
DBINC_P      XXX                            INDEX
DBINC_U1    XXX                            INDEX
TS                   XXX                            TABLE
TS_P             XXX                            INDEX
TS_U1          XXX                            INDEX
TS_U2          XXX                            INDEX
BS                 XXX                            TABLE
BS_P            XXX                            INDEX
BS_U2         XXX                            INDEX

已选择13行。

SQL> DROP TABLE XXX.DB;
DROP TABLE XXX.DB
               *
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用

 

在不知道表中约束的情况下，使用如下方法删除这些对象

 1.删除外键对象 2.删除主键 3.删除约束

select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||';'
from dba_constraints
where owner='XXX' and constraint_type='R';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
-----------------------------------------------------------------------------------------
alter table XXX.DBINC drop constraint DBINC_F1;
alter table XXX.DB drop constraint DB_F1;
alter table XXX.TS drop constraint TS_F1;
alter table XXX.BS drop constraint BS_F1;

select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||';'
from dba_constraints
where owner='XXX' and constraint_type='P';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
------------------------------------------------------------------------------
alter table XXX.DB drop constraint DB_P;
alter table XXX.DBINC drop constraint DBINC_P;
alter table XXX.TS drop constraint TS_P;
alter table XXX.BS drop constraint BS_P;

 select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||';'
 from dba_constraints
 where owner='XXX';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
----------------------------------------------------------------------------------------------------
alter table XXX.BS drop constraint BS_C_CONTROLFILE_INCLUDED;
alter table XXX.BS drop constraint BS_C_INCR_LEVEL;
alter table XXX.BS drop constraint BS_C_BCK_TYPE;
alter table XXX.BS drop constraint SYS_C004821;
alter table XXX.BS drop constraint SYS_C004820;
alter table XXX.BS drop constraint SYS_C004819;

.................................

SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_SEGMENTS WHERE OWNER='XXX';

SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
DB                                                                                TABLE
DBINC                                                                             TABLE
TS                                                                                TABLE
BS                                                                                TABLE

SELECT 'DROP '||SEGMENT_TYPE||' '||OWNER||'.'||SEGMENT_NAME||' PURGE;' FROM DBA_SEGMENTS WHERE OWNER='XXX'

'DROP'||SEGMENT_TYPE||''||OWNER||'.'||SEGMENT_NAME||';'
---------------------------------------------------------
DROP TABLE XXX.DB PURGE;
DROP TABLE XXX.DBINC PURGE;
DROP TABLE XXX.TS PURGE;
DROP TABLE XXX.BS PURGE;

SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE OWNER='XXX';

未选定行