Re: Weid exp/imp problem
Hi You are most likely importing a table with foreign key prior to importing the primary key that it points to. you can do export with constraints N (I think) . Recreate the constraints after import. Or export your tables in the right order so the primary keys get imported first (use a parfile) Jack "Daiminger, Helmut" <[EMAIL PROTECTED]>@fatcity.com on 19-12-2001 09:25:20 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table "TABELLEN" 37 rows imported . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported . . importing table "TMP$TEST" 1 rows imported . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported . . importing table"TMP_FUNKTIONS_SPALTEN" 0 rows imported . . importing table "USEREXIT" 5 rows imported . . importing table"USEREXIT_TYPE" 3 rows imported . . importing table "ZYKLUS" 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN" " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K" "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut === De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. === The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Weid exp/imp problem
[EMAIL PROTECTED] wrote: > > Hi > > You are most likely importing a table with foreign key prior to importing > the primary key that it points to. > you can do export with constraints N (I think) . Recreate the constraints > after import. > Or export your tables in the right order so the primary keys get imported > first (use a parfile) > > Jack Normally, the export dumps table CREATE statements, data, CREATE INDEX, CHECK, UNIQUE and PRIMARY KEY constraints, and foreign keys come at the very end, after ALL tables. It looks indeed like some primary keys were not defined on the target box in the first place. Helmut, you should take a look to DBA_CONSTRAINTS. > "Daiminger, Helmut" <[EMAIL PROTECTED]>@fatcity.com on > 19-12-2001 09:25:20 > > Please respond to [EMAIL PROTECTED] > > Sent by: [EMAIL PROTECTED] > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) > > Hi! > > I'm experiencing a weird problem here... I'm about to move one user's > object from the development box to a test box. The user's rights on both > boxes are identical. > > What I do is this: > - export user (using exp) from development. Works flawlessly. > - import user into the other box (user setup and tablespaces are identical) > > An I get the following errors which doesn't make a lot of sense to me... > ... > . . importing table "TABELLEN" 37 rows imported > > . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported > > . . importing table "TMP$TEST" 1 rows imported > > . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported > > . . importing table"TMP_FUNKTIONS_SPALTEN" 0 rows imported > > . . importing table "USEREXIT" 5 rows imported > > . . importing table"USEREXIT_TYPE" 3 rows imported > > . . importing table "ZYKLUS" 7 rows imported > > IMP-00017: following statement failed with ORACLE error 2270: > "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" > FOREIGN" > " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" > IMP-3: ORACLE error 2270 encountered > ORA-02270: no matching unique or primary key for this column-list > IMP-00017: following statement failed with ORACLE error 2270: > "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN > K" > "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" > IMP-3: ORACLE error 2270 encountered > ORA-02270: no matching unique or primary key for this column-list > ... > > Any ideas why this is happening? > > This is 8.1.7 on Sun Solaris. > > Thanks, > Helmut > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Weid exp/imp problem
Title: Weid exp/imp problem This error is coming because at the time of adding foreign key constraint , referencing primary key is not available. You can avoid this error by creating a similar schema in the user in which you are taking the import and then import data with parameter "ignore=Y". Kranti -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 19, 2001 1:55 PMTo: Multiple recipients of list ORACLE-LSubject: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table "TABELLEN" 37 rows imported . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported . . importing table "TMP$TEST" 1 rows imported . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported . . importing table "TMP_FUNKTIONS_SPALTEN" 0 rows imported . . importing table "USEREXIT" 5 rows imported . . importing table "USEREXIT_TYPE" 3 rows imported . . importing table "ZYKLUS" 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN" " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K" "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: Weid exp/imp problem
You can handle this by two ways. The first is running the imp in two times. And the second imp with rows=n and ignore=y. On the other hand, you can create all the objects with the constraints INITIALLY DEFERRED. With the precaution that you can not undo the mode of the constraint with an alter table command. Therefore, you could do it with INITIALLY IMMEDIATE the default mode, but it would hurt the performance of the import. To get the stmts of creation, you can run the imp with show=y and get all the stmts or if you have 9i you could use DBMS_METADATA package. Regards. --- kranti pushkarna <[EMAIL PROTECTED]> wrote: > This error is coming because at the time of adding > foreign key constraint , > referencing primary key is not available. > You can avoid this error by creating a similar > schema in the user in which > you are taking the import and then import data > with parameter "ignore=Y". > > > Kranti > > -Original Message- > Sent: Wednesday, December 19, 2001 1:55 PM > To: Multiple recipients of list ORACLE-L > > > > Hi! > > I'm experiencing a weird problem here... I'm about > to move one user's object > from the development box to a test box. The user's > rights on both boxes are > identical. > > What I do is this: > - export user (using exp) from development. Works > flawlessly. > - import user into the other box (user setup and > tablespaces are identical) > > An I get the following errors which doesn't make a > lot of sense to me... > ... > . . importing table "TABELLEN" > 37 rows imported > . . importing table "TABELLEN_ZUORDNUNGEN" > 28 rows imported > . . importing table "TMP$TEST" >1 rows imported > . . importing table "TMP_FUNKTIONS_PARAMETER" >0 rows imported > . . importing table"TMP_FUNKTIONS_SPALTEN" >0 rows imported > . . importing table "USEREXIT" >5 rows imported > . . importing table"USEREXIT_TYPE" >3 rows imported > . . importing table "ZYKLUS" >7 rows imported > IMP-00017: following statement failed with ORACLE > error 2270: > "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD > CONSTRAINT "BNGRZ_BNGR_FK" > FOREIGN" > " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" > ("ID") ENABLE NOVALIDATE" > IMP-3: ORACLE error 2270 encountered > ORA-02270: no matching unique or primary key for > this column-list > IMP-00017: following statement failed with ORACLE > error 2270: > "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD > CONSTRAINT "BNGRZ_OW_FK" FOREIGN > K" > "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE > NOVALIDATE" > IMP-3: ORACLE error 2270 encountered > ORA-02270: no matching unique or primary key for > this column-list > ... > > Any ideas why this is happening? > > This is 8.1.7 on Sun Solaris. > > Thanks, > Helmut > > = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Weid exp/imp problem
Title: Weid exp/imp problem Helmut: This could be due to the fact that constraints on those tables have not been defined yet. Since tables are imported in Alpha order, I would suggest performing a no-data import after this one is complete. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 3:25 AM To: Multiple recipients of list ORACLE-L Subject: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table "TABELLEN" 37 rows imported . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported . . importing table "TMP$TEST" 1 rows imported . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported . . importing table "TMP_FUNKTIONS_SPALTEN" 0 rows imported . . importing table "USEREXIT" 5 rows imported . . importing table "USEREXIT_TYPE" 3 rows imported . . importing table "ZYKLUS" 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN" " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K" "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: Weid exp/imp problem
Title: Weid exp/imp problem You may need to use consistent=y on the export to ensure that the view for the entire export run is from one snapshot in time. If your application is busy changing data during the export, then as export moves through the list of tables (alphabetically I think) then you'll get some tables at the beginning of the list from one time, and tables late in the list from another with possible referential problems just as exhibited below. hope this helps, Steve -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 19, 2001 2:25 AMTo: Multiple recipients of list ORACLE-LSubject: Weid exp/imp problem Hi! I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical. What I do is this: - export user (using exp) from development. Works flawlessly. - import user into the other box (user setup and tablespaces are identical) An I get the following errors which doesn't make a lot of sense to me... ... . . importing table "TABELLEN" 37 rows imported . . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported . . importing table "TMP$TEST" 1 rows imported . . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported . . importing table "TMP_FUNKTIONS_SPALTEN" 0 rows imported . . importing table "USEREXIT" 5 rows imported . . importing table "USEREXIT_TYPE" 3 rows imported . . importing table "ZYKLUS" 7 rows imported IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN" " KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270: "ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K" "EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE" IMP-3: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list ... Any ideas why this is happening? This is 8.1.7 on Sun Solaris. Thanks, Helmut