Trying to do a data only import into a table and hitting the error below :
ORA-39120: Table "XYZ"."LOOKUPS" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
The primary key in the table( which we are truncating) , seems to be referring to a another child table with foreign key.
Run below script to get the parent table , child table relation
set lines 200
COL CHILD_TABLE FOR a50
col CONSTRAINT_NAME for a50
col owner form a40
col FK_column form a40
col table_name form a50
select b.owner, b.table_name child_table, c.column_name FK_column, b.constraint_name
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner
and b.table_name=c.table_name
and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name
and b.constraint_type='R'
and a.owner='&owner'
and a.table_name='&table_name'
and a.CONSTRAINT_TYPE='P'
Enter the values and then do a manual - alter table disable constraint.
For tables with multiple constraints this is easier i.e.
select 'alter table ' || owner || '.' || table_name || ' disable constraint ' || constraint_name ||';' stmt from all_constraints
where r_constraint_name in (select constraint_name from all_constraints where table_name = 'LOOKUPS')
Run the output - remember to enable the constraints when complete - the db I was doing this was restricted so no issue of any others "hitting" the tables.
Import should now work.
