Oracle Error 2291 Encountered
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. sc1 is having ora-02291: integrity constraint all tables with data and constraints while sc2 is having only structure
Ora-00001
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 Export: Release 9.2.0.4.0 - Production on Fri May 20 09:01:03 2005 Copyright (c) 1982, 2002, Oracle Corporation. A
the database (or tablespace) owportal01, synadmin is the owner for the database owsynergy01, the default password for these two user is: dorado Installation asks for an a oracle user, and this example selects redcell. This selection also appears in the installed.properties file. User redcell is the owner for the database owdata01 (loaddb creates tablespace owdata01 http://www.orafaq.com/forum/t/46250/ under user redcell) as follows: loaddb -u system -w dorado Executing this command creates owportal01 and owsynergy01 loaddb -u system -w dorado -s so the complete backup/restore should includes In this example dorado is the database administrator's password, and tmdb60 is the SID: Backup exp system/dorado@tmdb60 owner=redcell file=redcell.dmp http://kb.doradosoftware.com/KB7x/1215BackupRestoreRedcellSynergywithOracle.htm exp system/dorado@tmdb60 owner=netview file=netview.dmp exp system/dorado@tmdb60 owner=synadmin file=synadmin.dmp Restore imp system/dorado@tmdb60 fromuser=redcell touser=redcell ignore=y file=redcell.dmp imp system/dorado@tmdb60 fromuser=netview touser=netview ignore=y file=netview.dmp imp system/dorado@tmdb60 fromuser=synadmin touser=synadmin ignore=y file=synadmin.dmp You may encounter an ORACLE 2291 error when using command line imp.(Best practice is to use Oracle RMAN.) For example: . importing table "RCC_TASK_USAGE_ENTITY" IMP-00019: row rejected due to ORACLE error 2291 IMP-00003: ORACLE error 2291 encountered ORA-02291: integrity constraint (REDCELL.FKE609020E14863754) violated - parent key not found The workaround for this is to find the foreign key reference table and import the parent table first then re-import the problematic table. For example: imp system/d0rad0 fromuser=redcell touser=redcell ignore=y constraints=n file=redcell_17012013.dmp tables=rcc_task_usage_entity There is no substitute for having a DBA. Such an administrator could tell you Oracle has improved on its previous import/export utility with RMAN and Data Pump. Oracle's manuals explain the use of these utilities.
Web and Mobile SQL*Plus Data Warehousing Installation Networking Backup Recovery Security Tuning PLSQL Oracle Applications Scripts Administration ORA-HOWTO Web and Mobile SQL*Plus Data Warehousing Installation Networking Backup Recovery Security Tuning PLSQL Oracle Applications Papers Administration ORA-HOWTO Web and Mobile SQL*Plus Data Warehousing Installation Networking Backup http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=33887 Recovery Security Tuning PLSQL Oracle Applications Forum All threads Best last month Links Oracle official Oracle http://www.dba-oracle.com/t_ora_02291_integrity_constraint_string_string_violated_parent_key_not_found.htm Technical Info Tools (free) Tools (commercial) Oracle FAQ Other DBA-sites Oracle development Not Oracle specific Oracle Blogs Imho Fqa Pirats | Start a new thread | All threads | Best last month | Question Most of my databases are on version 12.1 11.2 11.1 10.2 10.1 or lower Answer and see the results DBA Top 10 1 M. Cadot 35800 2 F. Diaz oracle error 34000 3 J. Schnackenberg 16400 4 B. Vroman 15450 5 A. Kavsek 15250 6 P. Wisse 14250 7 T. Boles 13500 8 M. Hidayathullah ... 10900 9 A. Hudspith 9200 10 A. Khan 4100 About Advertise here Download PLATOThe free tool for auditing and tuning your databaseVersion 55 now available Sep 02, 2016 The DBA-Village forum as RSS feed Site StatisticsEver registered users47788Total active users2117Act. users last 24h9Act. users last hour1Registered user hits last week756Registered user hits last month6432 Go oracle error 2291 up Will the error will affect the data? Next thread: Sql Trace Prev thread: ORA-30036 Message Score Author Date Hi , I took a export of one schema and import i...... daniesh shaikh Jul 16, 2008, 21:40 Hi Yes This table is a chaild table and ...... Ravindra Nookala Jul 16, 2008, 22:04 Thanks for the reply. how can we check the parent...... daniesh shaikh Jul 16, 2008, 22:20 Hi The parent table must be there in the sou...... Ravindra Nookala Jul 16, 2008, 22:58 one more thing only constraint is not get imported...... daniesh shaikh Jul 16, 2008, 23:06 one more question what is the reason that child t...... daniesh shaikh Jul 16, 2008, 23:09 Hi Dinesh. 1. Tell me how are you doing the impo...... Ravindra Nookala Jul 17, 2008, 00:01 Hi Ravindra, we first take the export of source d...... daniesh shaikh Jul 17, 2008, 14:34 Hi Dinesh. The below command will only create ...... Ravindra Nookala Jul 17, 2008, 16:57 Sorry Ravindra, I used the indexfile=name.sql ... daniesh shaikh Jul 17, 2008, 19:43 Follow up by mail Click here Subject: Will the error will affect the data? Author: daniesh shaikh, India Date: Jul 16, 2008, 21:40, 3020 days ago Os info: solaris Oracle info: oracle 9x and oracle 10x Message: Hi , I took a export of one schema and import it to other schema. user complains that some of the dat
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
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 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 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 i