I'm having trouble importing a table from one database 10.2.0.5 to another 11.2.0.3. When I first imported I get 1 error on creating 1 constraint:
impdp lego/password tables=estact directory=datapump dumpfile=estact.dmp
Import: Release 11.2.0.3.0 - Production on Wed Apr 2 13:25:22 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "LEGO"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "LEGO"."SYS_IMPORT_TABLE_01": lego/******** tables=estact directory=datapump dumpfile=estact.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LEGO"."ESTACT" 56.01 MB 612972 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (LEGO.EstOp_EstAct) - parent keys not found
Failing sql is:
ALTER TABLE "LEGO"."ESTACT" ADD CONSTRAINT "EstOp_EstAct" FOREIGN KEY ("ESTOP_ID")
REFERENCES "LEGO"."ESTOP" ("ESTOP_ID") ENABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "LEGO"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 13:25:28
The data loads however this foreign key is not there so I try creating it manually after, but get this error:
“ORA-02298: cannot validate (LEGO.EstOp_EstAct) – parent keys not found”
This table from the source has 3 foreign key constraints. The one I’m having an issue creating (above) is a constraint against itself.
I tried another approach. I exported and imported the meta data only and it completed successfully. However when I then exported and imported the DATA only, the data will not load now with the same error:
ORA-31693: Table data object "LEGO"."ESTACT" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-02291: integrity constraint (LEGO.EstOp_EstAct) violated - parent key not found
My question is do I need to do something different when working with a table with a foreign key constraint against itself?
Here is the DDL of the table from the source and the 3 foreign keys:
CREATE TABLE LEGO.ESTACT
(
ESTOP_ID NUMBER(10,0) NOT NULL,
OPCLASS_CODE VARCHAR2(10) NOT NULL,
ESTACT_FLAG NUMBER(5,0) NOT NULL,
COSTR_NO_EST NUMBER(5,0) NULL,
ESTACT_TIME NUMBER(10,0) NULL,
ESTACT_FIX_COST NUMBER(20,4) NOT NULL,
ESTACT_VAR_COST NUMBER(20,4) NOT NULL,
ESTACT_LAB_COST NUMBER(20,4) NOT NULL,
ESTACT_OEX_COST NUMBER(20,4) NOT NULL,
ESTACT_TOT_COST NUMBER(20,4) NOT NULL,
ESTACT_PRI_COST NUMBER(20,4) NOT NULL,
ESTACT_CM_PRICE NUMBER(20,4) NOT NULL,
ESTACT_PRI_RATE NUMBER(20,4) NOT NULL,
ESTACT_IS_PRICE NUMBER(5,0) NOT NULL,
ESTACT_BASE_QTY NUMBER NULL,
RULE_CODE_BASE_QTY VARCHAR2(10) NULL,
ESTACT_NUM_FORMS NUMBER(5,0) NULL,
ESTACT_FIXMIN NUMBER(10,0) NULL,
UNIT_CODE_BASE VARCHAR2(10) NULL,
UNIT_CODE_INPUT VARCHAR2(10) NULL,
ESTACT_AFLAG NUMBER(5,0) NOT NULL,
ESTACT_AOPRATE NUMBER NULL,
RULE_AOPRATE VARCHAR2(10) NULL,
ESTACT_AWARATE NUMBER NULL,
RULE_AWARATE VARCHAR2(10) NULL,
ESTACT_OPRATE NUMBER NULL,
ESTACT_WARATE NUMBER NULL,
RULE_CODE_COSTR VARCHAR2(10) NULL,
RULE_VAL_COSTR NUMBER(5,0) NULL,
RULE_CODE_FIXMIN VARCHAR2(10) NULL,
CONSTRAINT ESTACT_IDX0
PRIMARY KEY (ESTOP_ID,OPCLASS_CODE)
USING INDEX TABLESPACE INDEXES
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 128K
BUFFER_POOL DEFAULT)
ENABLE
VALIDATE
)
ORGANIZATION HEAP
TABLESPACE USERS
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
Here is the foreign key DDL. Note the 1st one is referencing the same table (the one I’m having an issue with)
ALTER TABLE LEGO.ESTACT
ADD CONSTRAINT "EstOp_EstAct"
FOREIGN KEY (ESTOP_ID)
REFERENCES LEGO.ESTOP (ESTOP_ID)
ENABLE
VALIDATE
/
ALTER TABLE LEGO.ESTACT
ADD CONSTRAINT "RuleCodeCost_URULE"
FOREIGN KEY (RULE_CODE_COSTR)
REFERENCES LEGO.URULE (URULE_CODE)
ENABLE
VALIDATE
/
ALTER TABLE LEGO.ESTACT
ADD CONSTRAINT "EstActURule_FixMin"
FOREIGN KEY (RULE_CODE_FIXMIN)
REFERENCES LEGO.URULE (URULE_CODE)
ENABLE
VALIDATE
/
Message was edited by: Koce