Oracle Error 2291 Import
Contents |
20 May 2005 08:47 hiral Messages: 3Registered: May 2005 Junior Member hi we are having two schemas sc1 and sc2 at two different places. ora-02291 integrity constraint sc1 is having all tables with data and constraints while sc2
Ora-00001
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]. Report message to a moderator Re: Problem while importing [message #120577 is a reply to message #120565] Fri, 20 May 2005 09:19 Mahesh Rajendran Messages: 10672Registered: March 2002 Location: oracleDocoVille Senior MemberAccount 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
problems If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Page 1 of 2 12 Last Jump to page: Results 1 to 10 of 14 Thread: Export/Import problems Tweet Thread Tools Show Printable Version Email this Page… Subscribe to http://www.orafaq.com/forum/t/46250/ this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 09-14-2006,09:35 AM #1 Chucks_k View Profile View Forum Posts Senior Member Join Date Dec 2001 Posts 337 Export/Import problems Hi all, I have done a FULL (full=y) export on a db. For the import i need to import into a schema. On the second http://www.dbasupport.com/forums/showthread.php?52701-Export-Import-problems db, i have dropped the user (cascade) and recreated the user with an empty schema. Now i generated the import with the indexfile option to generate the ddl: imp system file=expfull.dmp fromuser=test touser=test indexfile=index.sql statistics=none ignore=y In the index file i removed all the REM comments and replaced the default tablespace with the default tablespace for the user on the second db. I ran the import: imp system file=expfull.dmp fromuser=test touser=test statistics=none ignore=y I got the following errors (one of many) duringof the import: Column 1 3100 Column 2 Pieter Breughelstraat 837 Column 3 3029SK Column 4 Utrecht Column 5 Utrecht Column 6 NL IMP-00019: row rejected due to ORACLE error 2291 IMP-00003: ORACLE error 2291 encountered ORA-02291: integrity constraint (NIK.LOC_C_ID_FK) violated - parent key not foun d Now the questions i have are: Have I done it the wrong way? Do contraints have to be disabled? Do i do this by removing the 'enable constraints' bit in the index file generated? Any help will be much appreciated! Thanks, Chucks Reply
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog http://www.dba-oracle.com/t_ora_02291_integrity_constraint_string_string_violated_parent_key_not_found.htm
ORA-02291: integrity constraint violated-parent key not found tips Oracle Error Tips by Burleson Consulting (S. Karam) The Oracle docs note this on the ora-02291 error: ORA-02291: integrity constraint (string.string) violated - parent key not found Cause: A foreign key value has no matching primary key value. Action: Delete the oracle error foreign key or add a matching primary key. For an insert statement, this ORA-02291 error is common when you are trying to insert a child without a matching parent, as defined by a foreign key constraint. In that case, you need to add the parent row to the table and then re-insert your child table row.See the dba_constraints view to find the parent table.To fully oracle error 2291 understand [primary and foreign key constraints, see the book Easy Oracle Jumpstart, to explain these concepts: Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2016 All rights reserved by Burleson Oracle is the registered trademark of Oracle Corporation. �� <