| Problem while importing [message #120565] |
Fri, 20 May 2005 08:47  |
hiral
Messages: 3
Registered: May 2005
|
Junior Member |
|
|
hi
we are having two schemas sc1 and sc2 at two different places.
sc1 is having all tables with data and constraints while sc2 is having only structure of same tables with constraints but no data.
in sc1 there r some tables having self refferential integrity constraints ,i.e. P rimary and Foreign key constraints.
we are trying to tranfer data from Sc1 to Sc2 & for that we are using export and import utility of oracle.
when we import data from sc1.DMP to sc2, we phase problem while importing tables which have 'self refferential integrity constraints' b'coz import utility tries to import child row before parent row and finally is rejected on parent not found.
kindly give solution how to import all data to sc2.
thanking u
Hiral patel
Bhadresh patel [DBA-L&T].
|
|
|
|
| Re: Problem while importing [message #120577 is a reply to message #120565] |
Fri, 20 May 2005 09:19  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
the import should handle it.
Please see the session.
Else,
you can disable all the constraints before loading
load ( import with constraints=n)
enable the consraints.
--
-- set up the tables
--
scott@9i > alter table emp add constraint pk_eno primary key (empno);
Table altered.
scott@9i > alter table emp add constraint self_1 foreign key (mgr) references emp(empno);
Table altered.
--
-- Test the constraints
--
scott@9i > insert into emp values (111,'sam','newbie',9999,sysdate,100,10,10);
insert into emp values (111,'sam','newbie',9999,sysdate,100,10,10)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SELF_1) violated - parent key not found
scott@9i > insert into emp values (111,'sam','newbie',7698,sysdate,100,10,10);
1 row created.
scott@9i > commit;
Commit complete.
--
-- export the table.
--
scott@9i > !exp scott/tiger tables=(emp) indexes=y constraints=y statistics=none
Export: Release 9.2.0.4.0 - Production on Fri May 20 09:01:03 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
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 EMP 15 rows exported
Export terminated successfully without warnings.
--
-- import the table to another user!.
--
scott@9i > !imp scott/tiger fromuser=scott touser=mag
Import: Release 9.2.0.4.0 - Production on Fri May 20 09:08:25 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
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 MAG
. . importing table "EMP" 15 rows imported
About to enable constraints...
Import terminated successfully without warnings.
--
-- If the target table is already present.
--
scott@9i > truncate table mag.emp;
Table truncated.
--
-- Import with ignore = y.
--
scott@9i > !imp scott/tiger fromuser=scott touser=mag ignore=y
Import: Release 9.2.0.4.0 - Production on Fri May 20 09:11:37 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
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 MAG
. . importing table "EMP" 15 rows imported
About to enable constraints...
Import terminated successfully without warnings.
|
|
|
|
| Re: Problem while importing [message #120580 is a reply to message #120565] |
Fri, 20 May 2005 09:34 |
macdba
Messages: 27
Registered: May 2005
Location: US
|
Junior Member |
|
|
Hi
U have to disable the constraints and triggers b4 importing.
Execute an output of the following query in the importing scheman b4 u import. This will disable constraints. U can also disable triggers if u have any.
select 'ALTER TABLE ' || OWNER || '.' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ';' from dba_constraints where owner not like ('%SYS%')
Write a similar query to ENABLE the constraints after IMPORT.
Mak
|
|
|
|
| Re: Problem while importing [message #120623 is a reply to message #120565] |
Sat, 21 May 2005 00:10 |
hiral
Messages: 3
Registered: May 2005
|
Junior Member |
|
|
hi
we are not allowed to disable Sc2's constraints and we are exporting Sc1 with CONSTRAINTS=N,still import skips some rows as described below
----------------------------------------------------------------
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
- Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
Warning: the objects were exported by SC1, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character
set
. . importing table "TABLE1"
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SC2.FK_EMP_CODE) violated - parent key
not found
Column 1 A003M0002N0001 -- child Key
Column 2 A003M0002 -- Parent Key
Column 3 ad1
Column 4
Column 5 ad1
Column 6
Column 7
Column 8
Column 9
Column 10
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SC2.PK_EMP_CODE) violated
14 rows imported
Import terminated successfully with warnings.
----------------------------------------------------------------
kindly give solution how to import all data to sc2.
thanking u
Hiral patel
Bhadresh patel [DBA-L&T].
|
|
|
|
|
|
| Re: Problem while importing [message #120691 is a reply to message #120642] |
Sun, 22 May 2005 23:34 |
hiral
Messages: 3
Registered: May 2005
|
Junior Member |
|
|
thnks to Mr. Mahesh Rajendran and Mr. Mak.
we have solved our problem.
heartly thanks for showing your interest in solving our problem.
Hiral Patel
Bhadresh Patel [DBA-L&T]
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
