Home » RDBMS Server » Server Utilities » Is import handle relation between parent and child rows.
Is import handle relation between parent and child rows. [message #285510] Tue, 04 December 2007 23:19 Go to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Hello Experts,

I exported some of the rows from two tables using query option like follows. And then deleted those rows from those tables.


SQL> host exp scott/tiger tables=(emp,dept) query="""where deptno in (select
tno from dept where deptno=30)"""

Export: Release 9.2.0.1.0 - Production on Wed Dec 5 08:08:32 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            EMP          6 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          1 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

SQL> delete from emp where deptno=30;

6 rows deleted.

SQL> delete frome dept where deptno=30;
delete frome dept where deptno=30
       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> delete from dept where deptno=30;

1 row deleted.

SQL> commit;

Commit complete.



Now I want to imp the same rows into the same tables. But as you all knows the relationship between scott.emp and scott.dept(child and parent), is this relationship known to IMP. because it makes me run imp command twice.
First for all dept rows.
second for emp rows.

But imp does not imports into both the tables at the same time as follows.
SQL> host imp scott/tiger

Import: Release 9.2.0.1.0 - Production on Wed Dec 5 08:10:07 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Import file: EXPDAT.DMP >

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no > yes

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no > yes

. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7499
Column 2 ALLEN
Column 3 SALESMAN
Column 4 7698
Column 5 20-FEB-1981:00:00:00
Column 6 1600
Column 7 300
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7521
Column 2 WARD
Column 3 SALESMAN
Column 4 7698
Column 5 22-FEB-1981:00:00:00
Column 6 1250
Column 7 500
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7654
Column 2 MARTIN
Column 3 SALESMAN
Column 4 7698
Column 5 28-SEP-1981:00:00:00
Column 6 1250
Column 7 1400
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7698
Column 2 BLAKE
Column 3 MANAGER
Column 4 7839
Column 5 01-MAY-1981:00:00:00
Column 6 2850
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7844
Column 2 TURNER
Column 3 SALESMAN
Column 4 7698
Column 5 08-SEP-1981:00:00:00
Column 6 1500
Column 7 0
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7900
Column 2 JAMES
Column 3 CLERK
Column 4 7698
Column 5 03-DEC-1981:00:00:00
Column 6 950
Column 7
Column 8 30          0 rows imported
. . importing table                         "DEPT"          1 rows imported
About to enable constraints...
Import terminated successfully with warnings.

SQL> host imp scott/tiger

Import: Release 9.2.0.1.0 - Production on Wed Dec 5 08:10:34 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Import file: EXPDAT.DMP >

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no > yes

Import grants (yes/no): yes > y

Import table data (yes/no): yes >

Import entire export file (yes/no): no >
Username: scott

Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: emp

Enter table(T) or partition(T:P) name or . if done:

. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"          6 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL>


Please Help out on this.

Regards
Re: Is import handle relation between parent and child rows. [message #285514 is a reply to message #285510] Tue, 04 December 2007 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>But imp does not imports into both the tables at the same time as follows.
Yup. This is a known "issue" when using imp.

Alternatively imp loading data but CONSTRAINTS=NO
then imp ROWS=NO CONSTRAINTS=YES
Re: Is import handle relation between parent and child rows. [message #285545 is a reply to message #285514] Wed, 05 December 2007 00:33 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for your reply,

But Dear Anacedent, if you see my posted code, if i did not mention constraints=no in my first step of imp, it is inserting the rows in dept table,
and if i mention rows=no and constraints=yes in second step it is not inserting any rows. As you can see it here


SQL> host imp scott/tiger constraints=no ignore=yes

SQL> host imp scott/tiger constraints=no ignore=yes

Import: Release 9.2.0.1.0 - Production on Wed Dec 5 09:21:17 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7499
Column 2 ALLEN
Column 3 SALESMAN
Column 4 7698
Column 5 20-FEB-1981:00:00:00
Column 6 1600
Column 7 300
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7521
Column 2 WARD
Column 3 SALESMAN
Column 4 7698
Column 5 22-FEB-1981:00:00:00
Column 6 1250
Column 7 500
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7654
Column 2 MARTIN
Column 3 SALESMAN
Column 4 7698
Column 5 28-SEP-1981:00:00:00
Column 6 1250
Column 7 1400
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7698
Column 2 BLAKE
Column 3 MANAGER
Column 4 7839
Column 5 01-MAY-1981:00:00:00
Column 6 2850
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7844
Column 2 TURNER
Column 3 SALESMAN
Column 4 7698
Column 5 08-SEP-1981:00:00:00
Column 6 1500
Column 7 0
Column 8 30
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not foun
d
Column 1 7900
Column 2 JAMES
Column 3 CLERK
Column 4 7698
Column 5 03-DEC-1981:00:00:00
Column 6 950
Column 7
Column 8 30          0 rows imported
. . importing table                         "DEPT"          1 rows imported
About to enable constraints...
Import terminated successfully with warnings.


SECOND STEP: I supposed to get 14 rows after inserting, but i am get 8 only.


SQL> host imp scott/tiger constraints=yes rows=no ignore=yes

Import: Release 9.2.0.1.0 - Production on Wed Dec 5 09:27:21 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
About to enable constraints...
Import terminated successfully without warnings.

SQL> select count(*) from emp;

  COUNT(*)
----------
         8

SQL>


Please help

Regards

[Updated on: Wed, 05 December 2007 00:34]

Report message to a moderator

Re: Is import handle relation between parent and child rows. [message #285550 is a reply to message #285545] Wed, 05 December 2007 00:41 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Hello Anacedent,

I think in the second step of import rows must be rows=yes.

As You can see here.

SQL> host imp scott/tiger constraints=yes rows=yes ignore=yes

Import: Release 9.2.0.1.0 - Production on Wed Dec 5 09:35:35 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"          6 rows imported
. . importing table                         "DEPT"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 30
Column 2 SALES
Column 3 CHICAGO          0 rows imported
About to enable constraints...
Import terminated successfully with warnings.


AND THEN,

SQL> select count(*) from emp;

  COUNT(*)
----------
        14


THANKS, WAITING FOR YOUR FEEDBACK.
REGARDS
Re: Is import handle relation between parent and child rows. [message #285584 is a reply to message #285550] Wed, 05 December 2007 01:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In your export, why dont you just reverse the table order
SQL> host exp scott/tiger tables=(dept,emp) query="""where deptno in (select...


IMP should import in the order they were exported. If dept is first, the foreign key parent will be in place before emp is imported.

Ross Leishman
Re: Is import handle relation between parent and child rows. [message #285596 is a reply to message #285584] Wed, 05 December 2007 02:14 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for your reply,

But Dear rleishman, here we know that dept talbe is the parent table and emp table is child, hence we can change the order.

But what if i am exporting from many tables and i don't know which one is parent of what table.
Please clarify.


regards,
Re: Is import handle relation between parent and child rows. [message #285845 is a reply to message #285596] Wed, 05 December 2007 21:12 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Then disable constraints before you import.

Alternatively, run the SQL:
select b.table_name, a.table_name
from user_constraints a
join user_constraints b on a.r_constraint_name = b.constraint_name
where a.table_name in (...tab list...)
and b.table_name in (...tab list...)
to get a list of parent-child pairs.

Spool it to a file and run Unix TSORT over the file, then export the files in the order suggested by TSORT.

Ross Leishman
Previous Topic: How to use imp.exe
Next Topic: incremental export
Goto Forum:
  


Current Time: Thu Dec 08 21:54:54 CST 2016

Total time taken to generate the page: 0.06192 seconds