| Is import handle relation between parent and child rows. [message #285510] |
Tue, 04 December 2007 23:19  |
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 #285545 is a reply to message #285514] |
Wed, 05 December 2007 00:33  |
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 |
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 |
rleishman
Messages: 3728
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 #285845 is a reply to message #285596] |
Wed, 05 December 2007 21:12 |
rleishman
Messages: 3728
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...)
Spool it to a file and run Unix TSORT over the file, then export the files in the order suggested by TSORT.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
