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-00003: 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_NAME STATUS ------------------------------ -------- 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, 00000,"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). -- 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).
