Troubleshooting ORA-39083 & ORA-02270

Hi,
When my checked my impdp job logfile I have seen the error ORA-39083 & ORA-02270,
You see such error when the primary key of table is disabled while doing export.
Below is such scenario and the possible solution:


Error details from logfile of impdp:
------------------------------------

ORA-02270: no matching unique or primary key for this column-list
Failing sql is:
ALTER TABLE "TESTOOL"."TEST_LANDSCAPE_DETAIL_QKIT" ADD CONSTRAINT "PRLD_PRLM_FK" FOREIGN KEY ("PRLD_PRLM_FK") REFERENCES "PI OOL"."TEST_LANDSCAPE_MASTER_QKIT" ("PRLM_PK") DISABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:

Process of troubleshooting:
---------------------------

Process:
--------
Compare the source and target environment,Diagnose the issue and resolve it.

Step 1: Check the tables which are having primary key disabled
-------

SQL> select owner||' '||constraint_name||' '||constraint_type||' '||status from dba_constraints where table_name='TEST_LANDSCAPE_MASTER_QKIT';

OWNER||''||CONSTRAINT_NAME||''||CONSTRAINT_TYPE||''||STATUS
------------------------------------------------------------------------
TESTOOL PRLM_PK P DISABLED
TESTOOL SYS_C0015976 C DISABLED
TESTOOL SYS_C0015975 C DISABLED
TESTOOL SYS_C0015974 C DISABLED
TESTOOL SYS_C0015973 C DISABLED
TESTOOL SYS_C0015972 C DISABLED

6 rows selected.

SQL> select owner||' '||constraint_name||' '||constraint_type||' '||status from dba_constraints where table_name='TEST2_LINE_MASTER_QKIT';

OWNER||''||CONSTRAINT_NAME||''||CONSTRAINT_TYPE||''||STATUS
------------------------------------------------------------------------
TESTOOL PLM_PK P DISABLED
TESTOOL SYS_C0015986 C DISABLED
TESTOOL SYS_C0015985 C DISABLED
TESTOOL SYS_C0015984 C DISABLED
TESTOOL SYS_C0015983 C DISABLED
TESTOOL PLM_FK R DISABLED

6 rows selected.

Step 2:Enable the primary keys
------

SQL> ALTER TABLE TESTOOL.TEST_LANDSCAPE_MASTER_QKIT
2 enable CONSTRAINT PRLM_PK;

Table altered.

SQL> ALTER TABLE TESTOOL.TEST2_LINE_MASTER_QKIT
2 enable CONSTRAINT PLM_PK;

Table altered.

SQL> select owner||' '||constraint_name||' '||constraint_type||' '||status from dba_constraints where table_name='TEST_LANDSCAPE_MASTER_QKIT';

OWNER||''||CONSTRAINT_NAME||''||CONSTRAINT_TYPE||''||STATUS
------------------------------------------------------------------------
TESTOOL PRLM_PK P ENABLED
TESTOOL SYS_C0015976 C DISABLED
TESTOOL SYS_C0015975 C DISABLED
TESTOOL SYS_C0015974 C DISABLED
TESTOOL SYS_C0015973 C DISABLED
TESTOOL SYS_C0015972 C DISABLED

6 rows selected.


Step 3: Now do the export,copy the dump file and import in target Database.Remember to disable the primary keys at both source
and Target side.

In the final step copy the new dump file and import into target Database.This time you won't get ORA-02270.

Hope it helps,


Best regards,

Rafi.