Got ORA-02266 when import data in schema mode.
[oracle@test ~]$ impdp system/password schemas=hr,oe,sh content=data_only table_exists_action=truncate network_link=system_link_orcl
ORA-39120: Table "OE"."PRODUCT_INFORMATION" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You might consider to add data_options=skip_constraint_errors for this import, but this option cannot suppress referential constraints with table_exists_action=truncate. Therefore, you have to disable referential constraints manually.
Here is my sample script for disabling foreign key constraints.
[oracle@test ~]$ vi disable_constraints.sh
sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0 linesize 150 echo off
select 'alter table "' || owner || '"."' || table_name || '" disable constraint ' || constraint_name || ';' stmt from dba_constraints where r_owner in ('HR', 'OE', 'SH') and constraint_type = 'R' and status = 'ENABLED' order by owner, table_name;
set echo on feedback on
In the above, you can change the schema name for your condition.
Don’t forget to make it executable.
[oracle@test ~]$ chmod u+x disable_constraints.sh
Then execute the script.
[oracle@test ~]$ ./disable_constraints.sh
alter table "PM"."ONLINE_MEDIA" disable constraint LOC_C_ID_FK;
alter table "PM"."PRINT_MEDIA" disable constraint PRINTMEDIA_FK;
Now, you can import the data again. And don’t forget to enable them back once import is completed.
. . imported "OE"."PRODUCT_INFORMATION" 288 rows
Additionally, you may need to disable triggers before import as well.
If you don’t want to disable triggers or constraints, you should use table_exists_action=replace to dump the data instead. The import job will conduct the order of objects to be import.