NLS settings for Oracle

I. Introduce a concept:
NLS 'national Language Support (NLS) 'from the concept we can see that this parameter actually defines the Language environment for storing data in the database, when we set an nls, we actually specify some expressions specific to the language of Oracle when storing data. For example, we select chinese, then, the date format is set for how to store Chinese characters, sort by rules, and represent currencies.
　　
Ii. How to query the character sets used in our database
SQL> select * from V $ NLS_PARAMETERS;
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY RMB
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH. MI. SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH. MI. SSXFF AM
NLS_TIME_TZ_FORMAT HH. MI. SSXFF AMTZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH. MI. SSXFF AM TZR
NLS_DUAL_CURRENCY RMB
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Nls_language indicates the Chinese display mode. nls_characterset indicates the character set. In addition, date and time are the date and time formats, and currency indicates the currency format.
　　
Iii. Problems and Solutions
We have two main problems when using character sets.
　　
First, we will display Chinese characters in the query.
This is generally because the character set on the server side is different from the character set on the client side. You only need to change the two to solve the problem.
In windows, you need to change nls_lang in the local machine-software-oracle-home0 in the registry, and in unix, you need to change nls_lang in. profile or this. bash_profile (based on your shell.
Second, we prompt that the character set does not match when importing data. (Compatible)
Currently, I see three solutions:
1 :) exp/imp is a client product like SQL * plus, so its character set is set by the server's nls_lang. When exporting the backup export file with exp, Character Set settings are also stored in export file. This is the principle of changing the export file header when imp cannot be converted.
2 :) <= 8.1
Run SQL> create database character set US7ASCII before imp
* Create database character set US7ASCII
ERROR at line 1:
ORA-01031: insufficient privileges
---- You will find that the above error message is prompted during statement execution. In this case, you don't need to worry about it. In fact, the character set of the ORACLE database has been forcibly changed to US7ASCII, and then load data using the imp command. After the data is loaded, shut down the database and start up the database. log on to the ORACLE database with a valid user and run select * from V $ NLS_PARAMETERS at the SQL> command prompt, you can see that the character set of the ORACLE database has been restored. When you view a table with Chinese character data, the Chinese character is correctly displayed.
3 :) use a data pipeline to import data
In this way, I used the pb6 data pipeline to connect the data pipeline to the two databases that need to be imported and exported for data transmission. After the transmission, the Chinese characters are displayed as correct.
4 :) more data dictionary props $> 8.1
1) Use SQL * Plus connect to database as user SYS
2.) Issue this below command
SQL> UPDATE PROPS $ SET VALUE $ = 'xxx' WHERE NAME = 'nls _ CHARACTERSET ';
3.) Shutdown database and restart it
Finally, add a question about automatic conversion of character sets (in imp ):
Warning You can't change character set of current database that have number of bits greater than the new one. For example,
I understand 7-8 can, 8-8ican, 8-8cannot, 8-7cannot
US7ASCII-> WE8ISO8859P1 => Can
TH8TISASCII-> US7ASCII => Cannot
WE8ISO8859P1-> TH8TISASCII => Cannot
TH8TISASCII-> WE8ISO8859P1 => Can
US7ASCII-> TH8TISASCII => Can