RE: import from 8i to 9i /IMP-00003:ORA-02298
Update... Oracle filed a bug (#3083916) to resolve this and they suggested a workaround for the time being.. We have function based indexes on the associated PKs and dropping those indexes letting us enable the constraints. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Monday, July 14, 2003 10:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Just to update you all... I am not having any problems if I setup a db link from 9i to 8i and get the data then enable the constraints. Oracle asked me to create a test case for them. I have sent them all the information they have asked for. Thanks, Surendra -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 I did that already. Please see below.. ERROR 1: = IMP-00017: following statement failed with ORACLE error 2298:"ALTER TABLE "TWESAU" ENABLE CONSTRAINT "FK_LC_AU_SYSID""IMP-3: ORACLE error 2298 encounteredORA-02298: cannot validate (CMS.FK_LC_AU_SYSID) - parent keys not found ALTER TABLE CMS.TWESAU ADD CONSTRAINT FK_LC_AU_SYSIDFOREIGN KEY (AU_CASELOAD_SYSID) REFERENCES CMS.TWESLC (LC_CASELOAD_SYSID) ON DELETE CASCADE; Query on source database: select AU_CASELOAD_SYSIDfrom cms.TWESAUwhere AU_CASELOAD_SYSID not in (select LC_CASELOAD_SYSID from cms.TWESLC); No rows ERROR 2: ==IMP-00017: following statement failed with ORACLE error 2298:"ALTER TABLE "TWESCA" ENABLE CONSTRAINT "FK_CO_CA_SYSID""IMP-3: ORACLE error 2298 encounteredORA-02298: cannot validate (CMS.FK_CO_CA_SYSID) - parent keys not found ALTER TABLE CMS.TWESCA ADD CONSTRAINT FK_CO_CA_SYSIDFOREIGN KEY (CA_COMPANY_SYSID) REFERENCES CMS.TWESCO (CO_COMPANY_SYSID) ON DELETE CASCADE; Query on source database: select CA_COMPANY_SYSIDfrom cms.TWESCAwhere CA_COMPANY_SYSID not in (select CO_COMPANY_SYSID from cms.TWESCO ); No rows. -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 10:37 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 Surendra, Go back to the 8i database and run a query to see if parent records exist for all child records. It sounds like your source database is bad. Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 5:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 With all my today's trials(including import into 8i db on Sun)I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only)from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj
RE: import from 8i to 9i /IMP-00003:ORA-02298
Just to update you all... I am not having any problems if I setup a db link from 9i to 8i and get the data then enable the constraints. Oracle asked me to create a test case for them. I have sent them all the information they have asked for. Thanks, Surendra -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 I did that already. Please see below.. ERROR 1: = IMP-00017: following statement failed with ORACLE error 2298:"ALTER TABLE "TWESAU" ENABLE CONSTRAINT "FK_LC_AU_SYSID""IMP-3: ORACLE error 2298 encounteredORA-02298: cannot validate (CMS.FK_LC_AU_SYSID) - parent keys not found ALTER TABLE CMS.TWESAU ADD CONSTRAINT FK_LC_AU_SYSIDFOREIGN KEY (AU_CASELOAD_SYSID) REFERENCES CMS.TWESLC (LC_CASELOAD_SYSID) ON DELETE CASCADE; Query on source database: select AU_CASELOAD_SYSIDfrom cms.TWESAUwhere AU_CASELOAD_SYSID not in (select LC_CASELOAD_SYSID from cms.TWESLC); No rows ERROR 2: ==IMP-00017: following statement failed with ORACLE error 2298:"ALTER TABLE "TWESCA" ENABLE CONSTRAINT "FK_CO_CA_SYSID""IMP-3: ORACLE error 2298 encounteredORA-02298: cannot validate (CMS.FK_CO_CA_SYSID) - parent keys not found ALTER TABLE CMS.TWESCA ADD CONSTRAINT FK_CO_CA_SYSIDFOREIGN KEY (CA_COMPANY_SYSID) REFERENCES CMS.TWESCO (CO_COMPANY_SYSID) ON DELETE CASCADE; Query on source database: select CA_COMPANY_SYSIDfrom cms.TWESCAwhere CA_COMPANY_SYSID not in (select CO_COMPANY_SYSID from cms.TWESCO ); No rows. -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 10:37 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 Surendra, Go back to the 8i database and run a query to see if parent records exist for all child records. It sounds like your source database is bad. Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 5:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 With all my today's trials(including import into 8i db on Sun)I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only)from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints
RE: import from 8i to 9i /IMP-00003:ORA-02298
I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 5:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 With all my today's trials(including import into 8i db on Sun)I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only)from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra
RE: import from 8i to 9i /IMP-00003:ORA-02298
I suggest calling Oracle Support. Get a test case ready. Please post how the problem gets resolved. - Kirti --- [EMAIL PROTECTED] wrote: I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 5:25 PM To: Multiple recipients of list ORACLE-L With all my today's trials(including import into 8i db on Sun) I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only) from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:59 PM To: Multiple recipients of list ORACLE-L Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: import from 8i to 9i /IMP-00003:ORA-02298
Surendra, Go back to the 8i database and run a query to see if parent records exist for all child records. It sounds like your source database is bad. Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 5:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 With all my today's trials(including import into 8i db on Sun)I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only)from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra
Re: import from 8i to 9i /IMP-00003:ORA-02298
When all else fails... Log a TAR with Oracle Sit back and wait, while enjoying a few belts of quality Kentucky sipping Bourbon Read through the phone book and locate all the Ursulas in the greater Frankfort area...begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: import from 8i to 9i /IMP-00003:ORA-02298
I have opened TAR this morning. Sure, will let you know. Thank you all for your time. Surendra -Original Message- Sent: Wednesday, July 09, 2003 11:30 AM To: Multiple recipients of list ORACLE-L I suggest calling Oracle Support. Get a test case ready. Please post how the problem gets resolved. - Kirti --- [EMAIL PROTECTED] wrote: I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 5:25 PM To: Multiple recipients of list ORACLE-L With all my today's trials(including import into 8i db on Sun) I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only) from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:59 PM To: Multiple recipients of list ORACLE-L Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: import from 8i to 9i /IMP-00003:ORA-02298
I did that already. Please see below.. ERROR 1: = IMP-00017: following statement failed with ORACLE error 2298:"ALTER TABLE "TWESAU" ENABLE CONSTRAINT "FK_LC_AU_SYSID""IMP-3: ORACLE error 2298 encounteredORA-02298: cannot validate (CMS.FK_LC_AU_SYSID) - parent keys not found ALTER TABLE CMS.TWESAU ADD CONSTRAINT FK_LC_AU_SYSIDFOREIGN KEY (AU_CASELOAD_SYSID) REFERENCES CMS.TWESLC (LC_CASELOAD_SYSID) ON DELETE CASCADE; Query on source database: select AU_CASELOAD_SYSIDfrom cms.TWESAUwhere AU_CASELOAD_SYSID not in (select LC_CASELOAD_SYSID from cms.TWESLC); No rows ERROR 2: ==IMP-00017: following statement failed with ORACLE error 2298:"ALTER TABLE "TWESCA" ENABLE CONSTRAINT "FK_CO_CA_SYSID""IMP-3: ORACLE error 2298 encounteredORA-02298: cannot validate (CMS.FK_CO_CA_SYSID) - parent keys not found ALTER TABLE CMS.TWESCA ADD CONSTRAINT FK_CO_CA_SYSIDFOREIGN KEY (CA_COMPANY_SYSID) REFERENCES CMS.TWESCO (CO_COMPANY_SYSID) ON DELETE CASCADE; Query on source database: select CA_COMPANY_SYSIDfrom cms.TWESCAwhere CA_COMPANY_SYSID not in (select CO_COMPANY_SYSID from cms.TWESCO ); No rows. -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 10:37 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 Surendra, Go back to the 8i database and run a query to see if parent records exist for all child records. It sounds like your source database is bad. Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried it on my sun box, still same problem. Also I tried with no compressing/uncompressing of dmp file, no luck. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 5:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 With all my today's trials(including import into 8i db on Sun)I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only)from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra
RE: import from 8i to 9i /IMP-00003:ORA-02298
I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) == I have gone through the globalization support guide and concluded that I should not worry about charctersets if I am not using any special datatypes or NCHAR datatypes. Please advise me how to resolve this issue. Is character set any issue at all? Do I have to influence the import utility to use the characterset of my interest? I don't want to do this as I will have to do this each time I import. I am wondering if any bug is associated with this. Thanks for your help. Surendra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: import from 8i to 9i /IMP-00003:ORA-02298
Title: RE: import from 8i to 9i /IMP-3:ORA-02298 What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: import from 8i to 9i /IMP-00003:ORA-02298
Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) == I have gone through the globalization support guide and concluded that I should not worry about charctersets if I am not using any special datatypes or NCHAR datatypes. Please advise me how to resolve this issue. Is character set any issue at all? Do I have to influence the import utility to use the characterset of my interest? I don't want to do this as I will have to do this each time I import. I am wondering if any bug is associated with this. Thanks for your help. Surendra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: import from 8i to 9i /IMP-00003:ORA-02298
Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra
RE: import from 8i to 9i /IMP-00003:ORA-02298
Title: RE: import from 8i to 9i /IMP-3:ORA-02298 Hi Raj, First I tried doing as a DBA user now I tried using SYSTEM. But still the same problem. Thanks, Surendra -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:34 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra
RE: import from 8i to 9i /IMP-00003:ORA-02298
Yes, I am sure. Just to make sure, I have compared the counts from export log file and import log file. Also I compared it with import log file I did in to 8i database. What I did with two pass import is..first I have imported with constarints=n then I imported with ROWS=N CONSTARINTS=Y. So I am not doing any truncates. And to make sure again... I have tried troubleshooting one of the failed constraints with smallest table: Table TWESVS is having FK, FK_OF_VS_SYSID on column TWESVS.VS_OFF_SYSID referring to TWESOF.OF_OFF_SYSID. 1) The Error: IMP-3: ORACLE error 2298 encountered ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found 2) The constraint definition: ALTER TABLE CMS.TWESVS ADD CONSTRAINT FK_OF_VS_SYSID FOREIGN KEY (VS_OFF_SYSID) REFERENCES CMS.TWESOF (OF_OFF_SYSID) ON DELETE CASCADE; 3) The user_constarints entry SQL select constraint_name, status from user_constraints where constraint_name='FK_OF_VS_SYSID'; CONSTRAINT_NAMESTATUS -- FK_OF_VS_SYSID ENABLED 4) And I tried disabling and then enabling manually, it gave ORA-02298 error. Also I tried to catch the bad data into excetions. The data in TWESVS.VS_OFF_SYSID ranges from values 1 to 107 (with some values missing) and the data in TWESOF.OF_OFF_SYSID ranges from 1 to 109. And the following query returned no rows. Please help me what could be the reason. Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) == I have gone through the globalization support guide and concluded that I should not worry about charctersets if I am not using any special
RE: import from 8i to 9i /IMP-00003:ORA-02298
Sorry..I forgot to write the query... select count(*) from twesvs where VS_OFF_SYSID not in (select OF_OFF_SYSID from TWESOF); -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 5:10 PM To: Multiple recipients of list ORACLE-L Yes, I am sure. Just to make sure, I have compared the counts from export log file and import log file. Also I compared it with import log file I did in to 8i database. What I did with two pass import is..first I have imported with constarints=n then I imported with ROWS=N CONSTARINTS=Y. So I am not doing any truncates. And to make sure again... I have tried troubleshooting one of the failed constraints with smallest table: Table TWESVS is having FK, FK_OF_VS_SYSID on column TWESVS.VS_OFF_SYSID referring to TWESOF.OF_OFF_SYSID. 1) The Error: IMP-3: ORACLE error 2298 encountered ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found 2) The constraint definition: ALTER TABLE CMS.TWESVS ADD CONSTRAINT FK_OF_VS_SYSID FOREIGN KEY (VS_OFF_SYSID) REFERENCES CMS.TWESOF (OF_OFF_SYSID) ON DELETE CASCADE; 3) The user_constarints entry SQL select constraint_name, status from user_constraints where constraint_name='FK_OF_VS_SYSID'; CONSTRAINT_NAMESTATUS -- FK_OF_VS_SYSID ENABLED 4) And I tried disabling and then enabling manually, it gave ORA-02298 error. Also I tried to catch the bad data into excetions. The data in TWESVS.VS_OFF_SYSID ranges from values 1 to 107 (with some values missing) and the data in TWESOF.OF_OFF_SYSID ranges from 1 to 109. And the following query returned no rows. Please help me what could be the reason. Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset
RE: import from 8i to 9i /IMP-00003:ORA-02298
With all my today's trials(including import into 8i db on Sun)I used different export file than the one I have used other day. I am wondering if the ftp(I did it in bin mode only)from unix box to windows box caused something? Anyway, I got my Sun box ready with 9i and will know soon if the problem is between Unix and Windows. Thanks for your reply. Surendra -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 08, 2003 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: RE: import from 8i to 9i /IMP-3:ORA-02298 Could your export file be corrupt. There have been a few alerts on 8.1.7 exports producing bad dump files. One alert is Note:223399.1. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204 [EMAIL PROTECTED] 7/8/03 3:34:29 PM What userid is exporting data and importing data? Try exporting and importing as system. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Subject: RE: import from 8i to 9i /IMP-3:ORA-02298 I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra
Re: import from 8i to 9i /IMP-00003:ORA-02298
Can u check in source database if the constraint is set to enable novalidate . It might have some data integrity problem in old data . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:09 PM Yes, I am sure. Just to make sure, I have compared the counts from export log file and import log file. Also I compared it with import log file I did in to 8i database. What I did with two pass import is..first I have imported with constarints=n then I imported with ROWS=N CONSTARINTS=Y. So I am not doing any truncates. And to make sure again... I have tried troubleshooting one of the failed constraints with smallest table: Table TWESVS is having FK, FK_OF_VS_SYSID on column TWESVS.VS_OFF_SYSID referring to TWESOF.OF_OFF_SYSID. 1) The Error: IMP-3: ORACLE error 2298 encountered ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found 2) The constraint definition: ALTER TABLE CMS.TWESVS ADD CONSTRAINT FK_OF_VS_SYSID FOREIGN KEY (VS_OFF_SYSID) REFERENCES CMS.TWESOF (OF_OFF_SYSID) ON DELETE CASCADE; 3) The user_constarints entry SQL select constraint_name, status from user_constraints where constraint_name='FK_OF_VS_SYSID'; CONSTRAINT_NAMESTATUS -- FK_OF_VS_SYSID ENABLED 4) And I tried disabling and then enabling manually, it gave ORA-02298 error. Also I tried to catch the bad data into excetions. The data in TWESVS.VS_OFF_SYSID ranges from values 1 to 107 (with some values missing) and the data in TWESOF.OF_OFF_SYSID ranges from 1 to 109. And the following query returned no rows. Please help me what could be the reason. Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII
Re: import from 8i to 9i /IMP-00003:ORA-02298
Run the following command after both tables are loaded and the constraint is not enabled select vs_off_sysid from cms.twesvs where vs_off_sysid not in (select of_off_sysid from cms.twesof); [EMAIL PROTECTED] wrote: Yes, I am sure. Just to make sure, I have compared the counts from export log file and import log file. Also I compared it with import log file I did in to 8i database. What I did with two pass import is..first I have imported with constarints=n then I imported with ROWS=N CONSTARINTS=Y. So I am not doing any truncates. And to make sure again... I have tried troubleshooting one of the failed constraints with smallest table: Table TWESVS is having FK, FK_OF_VS_SYSID on column TWESVS.VS_OFF_SYSID referring to TWESOF.OF_OFF_SYSID. 1) The Error: IMP-3: ORACLE error 2298 encountered ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found 2) The constraint definition: ALTER TABLE CMS.TWESVS ADD CONSTRAINT FK_OF_VS_SYSID FOREIGN KEY (VS_OFF_SYSID) REFERENCES CMS.TWESOF (OF_OFF_SYSID) ON DELETE CASCADE; 3) The user_constarints entry SQL select constraint_name, status from user_constraints where constraint_name='FK_OF_VS_SYSID'; CONSTRAINT_NAMESTATUS -- FK_OF_VS_SYSID ENABLED 4) And I tried disabling and then enabling manually, it gave ORA-02298 error. Also I tried to catch the bad data into excetions. The data in TWESVS.VS_OFF_SYSID ranges from values 1 to 107 (with some values missing) and the data in TWESOF.OF_OFF_SYSID ranges from 1 to 109. And the following query returned no rows. Please help me what could be the reason. Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset
RE: import from 8i to 9i /IMP-00003:ORA-02298
I did that. -Original Message- Sent: Tuesday, July 08, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Run the following command after both tables are loaded and the constraint is not enabled select vs_off_sysid from cms.twesvs where vs_off_sysid not in (select of_off_sysid from cms.twesof); [EMAIL PROTECTED] wrote: Yes, I am sure. Just to make sure, I have compared the counts from export log file and import log file. Also I compared it with import log file I did in to 8i database. What I did with two pass import is..first I have imported with constarints=n then I imported with ROWS=N CONSTARINTS=Y. So I am not doing any truncates. And to make sure again... I have tried troubleshooting one of the failed constraints with smallest table: Table TWESVS is having FK, FK_OF_VS_SYSID on column TWESVS.VS_OFF_SYSID referring to TWESOF.OF_OFF_SYSID. 1) The Error: IMP-3: ORACLE error 2298 encountered ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found 2) The constraint definition: ALTER TABLE CMS.TWESVS ADD CONSTRAINT FK_OF_VS_SYSID FOREIGN KEY (VS_OFF_SYSID) REFERENCES CMS.TWESOF (OF_OFF_SYSID) ON DELETE CASCADE; 3) The user_constarints entry SQL select constraint_name, status from user_constraints where constraint_name='FK_OF_VS_SYSID'; CONSTRAINT_NAMESTATUS -- FK_OF_VS_SYSID ENABLED 4) And I tried disabling and then enabling manually, it gave ORA-02298 error. Also I tried to catch the bad data into excetions. The data in TWESVS.VS_OFF_SYSID ranges from values 1 to 107 (with some values missing) and the data in TWESOF.OF_OFF_SYSID ranges from 1 to 109. And the following query returned no rows. Please help me what could be the reason. Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Are your sure that the parent table data is getting imported fully? Number of exported rows match the number of imported rows? During your subsequent import attempts, are you truncating the table? - Kirti --- [EMAIL PROTECTED] wrote: I tried importing data first then enabling constraints, same problem. I have disabled(it is not enabled to begin with but reporting 'ENABLED' in USER_CONSTRAINTS) one of those constraints and tried to enable it manually. Same error. And I have checked the data again. It is having parent keys. Thanks, Surendra -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Hi Kirti, As I have mentioned, my data is clean. I could import this data into an existing 8i database. Do you still think that importing data in two passes would fix it? Anything changed form 8i to 9i regarding iport process? I tried catching the invalid data into excetions and did cross checking and observed that I have valid data. I mean to say I could not enable those failed constarints manually as well. Surprisingly my STATUS column in USER_CONSTARINTS tables reads 'ENABLED' for those failed constarints also. Am I missing something? Thanks, Surendra -Original Message- Sent: Tuesday, July 08, 2003 12:44 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in
Re: import from 8i to 9i /IMP-00003:ORA-02298
I don't think this is related to NLS stuff... 02298, 0,cannot validate (%s.%s) - parent keys not found // *Cause: an alter table validating constraint failed because the table has // orphaned child records. // *Action: Obvious I suggest cleaning up target database (drop target tables, indexes, etc). Re-Create all target tables (empty). Do not enable constraints (disable them if already enabled). Re-import with ignore=y, and then enable constraints. Oracle imports tables in table_name order for the schema. Enabled RI constraints can pose a problem. - Kirti --- [EMAIL PROTECTED] wrote: Hello All, We are working on migrating 8i databases to 9i. As Sun boxes are not available yet, I have decided to do some experiments on my PC. I am trying to import into 9i database using export dump file of 8i database. I have my 8i database created in US7ASCII and I have created 9i database also in US7ASCII with UTF8, just to make sure that I won't hit any conversion issues. When I tried to import the data into this newly created database it is giving me some strange ORA-02298: cannot validate (CMS.FK_OF_VS_SYSID) - parent keys not found errors. I have verified that the data in 8i database is valid. I have successfully imported into an existing 8i database with no problem. I am wondering if the Character set conversion the import utility is doing making any difference. The following are the messages I am getting when I invoked import: == import done in WE8MSWIN1252 character set and UTF8 NCHAR character set import server uses US7ASCII character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) export server uses US7ASCII NCHAR character set (possible ncharset conversion) == I have gone through the globalization support guide and concluded that I should not worry about charctersets if I am not using any special datatypes or NCHAR datatypes. Please advise me how to resolve this issue. Is character set any issue at all? Do I have to influence the import utility to use the characterset of my interest? I don't want to do this as I will have to do this each time I import. I am wondering if any bug is associated with this. Thanks for your help. Surendra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).