If export has been done at the table level and If child table (Table with foriegn key) has been exported first and parent table later ( Table with primary key) then there will be issue at importing because import of tables takes place in the same order as export has taken .There will be constraints error in importing child tables. In that case we have to import first all for parent table and in after that import chid tables . i.e we have to perform import twice. Once the parents tables and second time child tables So to avoid this workaround, one should be prudent in picking the tables order in export.
Example:
In the given eample table x have foriegn key reference with the table y. Tables export takes in the order x and y.
exp scott/tiger@mediamat FILE=/home/oracle/expdrec/exp.dmp LOG=/home/oracle/expdrec/exp.LOG CONSISTENT=y tables=x,y
Export: Release 10.2.0.4.0 - Production on Mon May 9 09:47:52 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table X 2 rows exported
. . exporting table Y 2 rows exported
Export terminated successfully without warnings.
[oracle@mediamath-server archs]$ imp impuser/impuser@mediamat FILE=/home/oracle/expdrec/exp.dmp LOG=/home/oracle/expdrec/exp.LOG ignore=y tables=y,x fromuser=scott touser=impuser
Import: Release 10.2.0.4.0 - Production on Mon May 9 11:26:26 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into IMPUSER
. . importing table "X" 127136 rows imported
. . importing table "Y" 127209 rows imported
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "Y" ADD FOREIGN KEY ("AA_ID") REFERENCES "X" ("AA_ID") ENABLE"
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (IMPUSER.SYS_C0059272) - parent keys not found
Import terminated successfully with warnings.
No comments:
Post a Comment