[oracle@INP01 backup]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 22 09:43:02 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> col owner for a20
select OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
R_OWNER,
R_CONSTRAINT_NAME
from dba_constraints
where TABLE_NAME in ('MONTH_DIM');SQL> 2 3 4 5 6 7 8
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
-------------------- ------------------------------ - ------------------------------ ------------------------------ ------------------------------
USER01 SYS_C00286633 C MONTH_DIM
USER01 SYS_C00286634 C MONTH_DIM
USER01 SYS_C00286635 C MONTH_DIM
USER01 SYS_C00286636 C MONTH_DIM
USER01 SYS_C00286637 C MONTH_DIM
USER01 MONTH_DIM_PK P MONTH_DIM
6 rows selected.
SQL> col owner for a20
select OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
R_OWNER,
R_CONSTRAINT_NAME
from dba_constraints
where R_OWNER='USER01' and R_CONSTRAINT_NAME in ('MONTH_DIM_PK');SQL> 2 3 4 5 6 7 8
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
-------------------- ------------------------------ - ------------------------------ ------------------------------ ------------------------------
USER01 FK_DISTRIBUTION_FACT_MONTH_ENT R AP_DISTRIBUTION_FACT USER01 MONTH_DIM_PK
USER01 FK_DISTRIBUTION_FACT_MONTH_PD R AP_DISTRIBUTION_FACT USER01 MONTH_DIM_PK
USER01 FK_TRANSMITTAL_FACT_MONTH_ENT R AP_TRANSMITTAL_FACT USER01 MONTH_DIM_PK
USER01 FK_TRANSMITTAL_FACT_MONTH_PD R AP_TRANSMITTAL_FACT USER01 MONTH_DIM_PK
USER01 FK_PO_PAYMENT_FACT_MONTH_ENTER R PO_PAYMENT_FACT USER01 MONTH_DIM_PK
This info can be combined in a single query as below.
SQL> select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
2 from all_constraints a, all_constraints b
3 where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
4 5 and a.r_owner = b.owner
6 and b.table_name in ('MONTH_DIM')
order by a.constraint_name; 7
'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME||';'
----------------------------------------------------------------------------------------------------------------------------
alter table USER01.AP_DISTRIBUTION_FACT disable constraint FK_DISTRIBUTION_FACT_MONTH_ENT;
alter table USER01.AP_DISTRIBUTION_FACT disable constraint FK_DISTRIBUTION_FACT_MONTH_PD;
alter table USER01.PO_PAYMENT_FACT disable constraint FK_PO_PAYMENT_FACT_MONTH_ENTER;
alter table USER01.AP_TRANSMITTAL_FACT disable constraint FK_TRANSMITTAL_FACT_MONTH_ENT;
alter table USER01.AP_TRANSMITTAL_FACT disable constraint FK_TRANSMITTAL_FACT_MONTH_PD;
SQL> alter table USER01.AP_DISTRIBUTION_FACT disable constraint FK_DISTRIBUTION_FACT_MONTH_ENT;
alter table USER01.AP_DISTRIBUTION_FACT disable constraint FK_DISTRIBUTION_FACT_MONTH_PD;
alter table USER01.PO_PAYMENT_FACT disable constraint FK_PO_PAYMENT_FACT_MONTH_ENTER;
alter table USER01.AP_TRANSMITTAL_FACT disable constraint FK_TRANSMITTAL_FACT_MONTH_ENT;
alter table USER01.AP_TRANSMITTAL_FACT disable constraint FK_TRANSMITTAL_FACT_MONTH_PD;
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
Run the import again.
