数据导入时对外键约束的处理

DUMP文件的某些表可能含有外键，因此对表的导入有顺序要求，可以用以下脚本做一个基本过滤

SQL> select ' alter table '||table_name||' disable constraint '|| constraint_name||';' from user_constraints where
  2  constraint_type='R' union select 'alter table '||uca.table_name||' disable constraint '||uca.constraint_name||';'
  3  from
  4  user_constraints uca,
  5  (select constraint_name
  6  from user_constraints
  7  where constraint_type in ('P','U')
  8  ) tmp
  9  where uca.constraint_type='R'
 10  and tmp.constraint_name=uca.r_constraint_name;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
 alter table ATTRIBUTE_TRANSFORMATIONS$ disable constraint ATTRIBUTE_TRANSFORMAT
IONS_FK;


 alter table DAM_CONFIG_PARAM$ disable constraint DAM_CONFIG_PARAM_FK1;
 alter table DBFS$_MOUNTS disable constraint SYS_C003356;
 alter table DBFS_SFS$_FS disable constraint SYS_C003398;
 alter table DBFS_SFS$_FSTP disable constraint SYS_C003424;
 alter table DBFS_SFS$_SNAP disable constraint SYS_C003390;
 alter table DBFS_SFS$_VOL disable constraint SYS_C003381;
 alter table DBMS_PARALLEL_EXECUTE_CHUNKS$ disable constraint FK_DBMS_PARALLEL_E
XECUTE_1;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------


 alter table HS$_CLASS_CAPS disable constraint HS$_CLASS_CAPS_FK1;
 alter table HS$_CLASS_CAPS disable constraint HS$_CLASS_CAPS_FK2;
 alter table HS$_CLASS_DD disable constraint HS$_CLASS_DD_FK1;
 alter table HS$_CLASS_DD disable constraint HS$_CLASS_DD_FK2;
 alter table HS$_CLASS_INIT disable constraint HS$_CLASS_INIT_FK1;
 alter table HS$_FDS_INST disable constraint HS$_FDS_INST_FK1;
 alter table HS$_INST_CAPS disable constraint HS$_INST_CAPS_FK1;
 alter table HS$_INST_CAPS disable constraint HS$_INST_CAPS_FK2;
 alter table HS$_INST_DD disable constraint HS$_INST_DD_FK1;
 alter table HS$_INST_DD disable constraint HS$_INST_DD_FK2;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
 alter table HS$_INST_INIT disable constraint HS$_INST_INIT_FK1;
 alter table HS$_PARALLEL_HISTOGRAM_DATA disable constraint HS_PARALLEL_HISTOGRA
M_DATA_FK;


 alter table HS$_PARALLEL_PARTITION_DATA disable constraint HS_PARALLEL_PARTITIO
N_DATA_FK;


 alter table HS$_PARALLEL_SAMPLE_DATA disable constraint HS_PARALLEL_SAMPLE_DATA
_FK;


 alter table REGISTRY$ disable constraint REGISTRY_PARENT_FK;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
 alter table REGISTRY$DEPENDENCIES disable constraint DEPENDENCIES_FK;
 alter table REGISTRY$DEPENDENCIES disable constraint DEPENDENCIES_REQ_FK;
 alter table REGISTRY$PROGRESS disable constraint REGISTRY_PROGRESS_FK;
 alter table REGISTRY$SCHEMAS disable constraint REGISTRY_SCHEMA_FK;
 alter table WRM$_SNAPSHOT disable constraint WRM$_SNAPSHOT_FK;
alter table ATTRIBUTE_TRANSFORMATIONS$ disable constraint ATTRIBUTE_TRANSFORMATI
ONS_FK;


alter table DAM_CONFIG_PARAM$ disable constraint DAM_CONFIG_PARAM_FK1;
alter table DBFS$_MOUNTS disable constraint SYS_C003356;
alter table DBFS_SFS$_FS disable constraint SYS_C003398;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter table DBFS_SFS$_FSTP disable constraint SYS_C003424;
alter table DBFS_SFS$_SNAP disable constraint SYS_C003390;
alter table DBFS_SFS$_VOL disable constraint SYS_C003381;
alter table DBMS_PARALLEL_EXECUTE_CHUNKS$ disable constraint FK_DBMS_PARALLEL_EX
ECUTE_1;


alter table HS$_CLASS_CAPS disable constraint HS$_CLASS_CAPS_FK1;
alter table HS$_CLASS_CAPS disable constraint HS$_CLASS_CAPS_FK2;
alter table HS$_CLASS_DD disable constraint HS$_CLASS_DD_FK1;
alter table HS$_CLASS_DD disable constraint HS$_CLASS_DD_FK2;
alter table HS$_CLASS_INIT disable constraint HS$_CLASS_INIT_FK1;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter table HS$_FDS_INST disable constraint HS$_FDS_INST_FK1;
alter table HS$_INST_CAPS disable constraint HS$_INST_CAPS_FK1;
alter table HS$_INST_CAPS disable constraint HS$_INST_CAPS_FK2;
alter table HS$_INST_DD disable constraint HS$_INST_DD_FK1;
alter table HS$_INST_DD disable constraint HS$_INST_DD_FK2;
alter table HS$_INST_INIT disable constraint HS$_INST_INIT_FK1;
alter table HS$_PARALLEL_HISTOGRAM_DATA disable constraint HS_PARALLEL_HISTOGRAM
_DATA_FK;


alter table HS$_PARALLEL_PARTITION_DATA disable constraint HS_PARALLEL_PARTITION
_DATA_FK;


'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------


alter table HS$_PARALLEL_SAMPLE_DATA disable constraint HS_PARALLEL_SAMPLE_DATA_
FK;


alter table REGISTRY$ disable constraint REGISTRY_PARENT_FK;
alter table REGISTRY$DEPENDENCIES disable constraint DEPENDENCIES_FK;
alter table REGISTRY$DEPENDENCIES disable constraint DEPENDENCIES_REQ_FK;
alter table REGISTRY$PROGRESS disable constraint REGISTRY_PROGRESS_FK;
alter table REGISTRY$SCHEMAS disable constraint REGISTRY_SCHEMA_FK;
alter table WRM$_SNAPSHOT disable constraint WRM$_SNAPSHOT_FK;


56 rows selected.