Edit: Please close this topic.

Unfortunately, this issue can't be resolved as it turned out the question is very off-base to begin with. The actual issue was completely unrelated to the question I asked.

We are experiencing a data inconsistency issue after importing an exported Oracle 9i database into Oracle 11g.

Is there a setting that might facilitate this process better? A "compatibility-mode" settings or group of settings of sorts?

I'm looking for an extra lead that might help our DBAs, who are a little new to these sorts of issues I think, as this import falls outside of their normal process.

Thanks in advance for any help! Happy to provide more information as needed.

  • 1
    Can you describe the data issues? Are you using the same character sets to export / import the data?
    – tsells
    Jan 24, 2012 at 0:28
  • tsells -- the issue is that they have numerical values for some fields, and we have 0s. The data is a huge proprietary IBM system, but is there some setting where we can define the character sets? They may be using this setting already, but my background isn't in DBA skills or Oracle so I'm not sure offhand. Jan 24, 2012 at 0:49
  • The character sets are defined on the databases when they are created. You should confirm these match up and any potential conversion issues are okay. For the fields that have the numerical values - what is the data type? Is it a number or is a number stored as a string?
    – tsells
    Jan 24, 2012 at 4:42
  • I have flagged this question as not a real question as it cannot be reliably answered. The real issue was very far off base from the topic of this question. Thank you to all who attempted to help, but I think it's best that this one goes away for the benefit of the community. Jan 24, 2012 at 21:00
  • 1
    closed at the request of the OP :-) Jan 24, 2012 at 21:59

2 Answers 2


Take a look at the COMPATIBLE initialization parameter both here and here. These links are more to do with when you upgrade a database rather than import into a higher version but it should help if, perhaps, you can import your data into a cloned 9i database (so as not to mess up your original database) then upgrade that with the compatibility setting on to 9i.


Ok, since the source database is 9i, this is traditional export/import.

  1. Do you still have access to both the 9i and 11g databases? If so, connect to each one and do:

    show parameter nls

    (NLS stands for National Language Support, and showing all nls related parameters should tell you the characterset, as well as a few other details that may be relevant.)

  2. When running the export and import, did you correctly set the NLS_LANG environment variable?

  3. You mentioned numerical values, are these NUMBER datatypes? I assume you're using exp/imp to create the objects, and not just move the data? If that's not the case, are you sure all datatypes of all columns match across all tables?

Well, that's a start. If you provide more specific details, we may be able to provide a more complete answer.

Hope that helps.

Not the answer you're looking for? Browse other questions tagged or ask your own question.