ORA-02266
ORA-02266 means that you can't truncate the table because reference keys constraint still depend on the data of the table.
Truncate a Single Table
Let's see a case that throws ORA-02266.
SQL> conn hr/hr
Connected.
SQL> truncate table employees;
truncate table employees
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution to ORA-02266
To solve ORA-02266, we need to know what reference keys are preventing us from truncating the table and furthermore, we should disable them.
Connect as a privileged user, this is because a privileged user can find all reference keys to the table in the database globally.
SQL> conn / as sysdba
Connected.
We compose disabling reference key statements for this table.
SQL> set hea off;
SQL> select 'alter table "' || owner || '"."' || table_name || '" disable constraint "' || constraint_name ||'";' stmt from all_constraints where r_owner= 'HR' and r_constraint_name in (select constraint_name from all_constraints where owner = 'HR' and table_name = 'EMPLOYEES') order by 1;
alter table "HR"."DEPARTMENTS" disable constraint "DEPT_MGR_FK";
alter table "HR"."EMPLOYEES" disable constraint "EMP_MANAGER_FK";
alter table "HR"."JOB_HISTORY" disable constraint "JHIST_EMP_FK";
alter table "OE"."CUSTOMERS" disable constraint "CUSTOMERS_ACCOUNT_MANAGER_FK";
alter table "OE"."ORDERS" disable constraint "ORDERS_SALES_REP_FK";
Disable each reference key constraint one by one.
SQL> alter table "HR"."DEPARTMENTS" disable constraint "DEPT_MGR_FK";
Table altered.
SQL> alter table "HR"."EMPLOYEES" disable constraint "EMP_MANAGER_FK";
Table altered.
SQL> alter table "HR"."JOB_HISTORY" disable constraint "JHIST_EMP_FK";
Table altered.
SQL> alter table "OE"."CUSTOMERS" disable constraint "CUSTOMERS_ACCOUNT_MANAGER_FK";
Table altered.
SQL> alter table "OE"."ORDERS" disable constraint "ORDERS_SALES_REP_FK";
Table altered.
We can truncate the table now.
SQL> truncate table hr.employees;
Table truncated.
ORA-02266 is solved.
Later on, you may like to enable those constraints back.
Truncate a Bunch of Tables
Got ORA-02266 when import data in schema mode with TABLE_EXISTS_ACTION=TRUNCATE.
[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 to ORA-02266
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.