Oracle CharacterSet

1. What is impact of NLS_LANG variable setting of user session while import/export the data ?

On export, the data will be converted from the database character set to the character set specified by NLS_LANG. In import, the database will assume that the data is in the character set specified by NLS_LANG and use that value to perform the conversion to the database character set if the two values to not match.

2. Why do we need to set this NLS_LANG user session variable before export/import ?

If your database character set is the same as your OS, you don't necessarily have to set NLS_LANG. For instance, if you have a US7ASCII db, and your OS locale is set to AMERICA_AMERICAN.US7ASCII, there won't be any problems. The only time it's really important to set this is when the db and OS settings don't match.

3.If NLS_LANG variable is not set (doesnot have any value) what would happen ?

If your database character set doesn't match your OS, the data could be garbled because the db will incorrectly transcode the data on import/export.

4. If I have to set NLS_LANG varible, what should I set it to?

Depends on what your database character set is set to (see below).

5. How can I see the characterset of my database?

select * from nls_database_parameters and look for the value set for the NLS_CHARACTERSET parameter. Don't get confused by the NLS_NCHAR_CHARACTERSET, that's for NCHAR datatypes.

So, for instance, if your NLS_CHARACTERSET value is set to UTF8, you would set NLS_LANG to .UTF8 (the dot is important because that's actually shorthand for territory_language.characterset, or language_territory.characterset, I can never remember which comes first. In any case, use the dot). For example:

setenv NLS_LANG .UTF8

6. Where can I get more info about database charaterset and What are the valid values for database characterset and NLS_LANG varibale ?

It's all in the Oracle documentation.