使用EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK分析ORA-02429错误原因

xifenfei

SQL> drop tablespace t1 including contents and datafiles;
drop tablespace t1 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>  EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('T1');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index TOPSTAR.BMS_ACC_SEL_PK in tablespace T1 enforces primary constraints  of table TOPSTAR.BMS_ACC_SEL_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_ACC_SEL_DEF_IDX in tablespace T1 points to table TOPSTAR.BMS_ACC_SEL_DEF in tablespace USERS.
ORA-39908: Index TOPSTAR.BMS_BATCH_DEF_PK in tablespace T1 enforces primary constraints  of table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_1_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_2_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_3_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_4_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_GOODSID_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_INPUTDATE_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_SORTNO_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39907: Index TOPSTAR.BMS_BATCH_DEF_SOURCEID_IDX in tablespace T1 points to table TOPSTAR.BMS_BATCH_DEF in tablespace USERS.
ORA-39908: Index TOPSTAR.BMS_BATCH_NO_PK in tablespace T1 enforces primary constraints  of table TOPSTAR.BMS_BATCH_NO in tablespace USERS.
ORA-39908: Index TOPSTAR.BMS_BUSICLASSET_DEF_PK in tablespace T1 enforces primary constraints  of table TOPSTAR.BMS_BUSICLASSSET_DEF in tablespace USE
RS.

有部分表和index 不在同一个表空间，无法只删除t1表空间（因为表在users中，而index在t1中），删除掉users表空间中的table，然后再删除表空间即可

SQL> drop tablespace t1 including contents and datafiles;

Tablespace dropped.