Data Pump

How to Disable All Referencing Constraints Before Importing Data

Disable Constraints

Before you can import with table_exists_action=truncate, you have to disable all referencing constraints. Otherwise, you may get the error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Suppose you want to import 3 schemas, HR, SH, OE.

Compose disabling statement.

SQL> set heading off feedback off pagesize 0 linesize 150 echo off
SQL> spool /home/oracle/disable_references.sql
SQL> select 'alter table "' || owner || '"."' || table_name || '" disable constraint ' || constraint_name || ';' stmt from dba_constraints where r_owner in ('HR', 'SH', 'OE') and constraint_type = 'R' and status = 'ENABLED' order by owner, table_name;
SQL> spool off
SQL> exit

Modify the statements and save a copy for enabling statements later.

[oracle@test ~]$ vi disable_references.sql
[oracle@test ~]$ cp -p disable_references.sql enable_references.sql
[oracle@test ~]$ vi enable_references.sql

Now, we can disable all the related references for data import.

SQL> @/home/oracle/disable_references.sql;

Don’t forget to enable those references after importing.

SQL> @/home/oracle/enable_references.sql;

Leave a Reply

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