Data Pump

How to Resolve ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ORA-02266

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.

Solution

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
spool /home/oracle/disable_constraint_test.sql
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;
spool off
set echo on feedback on
@/home/oracle/disable_constraint_test.sql
exit
EOF

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;

Table altered.


Table altered.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *