How to modify the Oracle character set in the RAC Environment

Non-RAC Environment Character Set Modification

1.1 Oracle Character Set Problem Summary

1.1.1 concept of oracle Character Set

The oracle character set is a collection of symbols for the interpretation of byte data. It can be divided into different sizes and have an inclusive relationship. ORACLE supports the national language architecture, allowing you to store, process, and retrieve data in a localized language. It makes database tools, error messages, sorting order, date, time, currency, numbers, and calendar automatically adapt to localization languages and platforms.

The most important parameter that affects the character set of oracle databases is the NLS_LANG parameter. Format: NLS_LANG = language_territory.charset

Where: Language specifies the Language of the server message, territory specifies the date and digital format of the server, and charset specifies the character set. For example, AMERICAN _ AMERICA. ZHS16GBK. From the composition of NLS_LANG, we can see that the real impact on the database character set is actually the third part. Therefore, if the character set between the two databases is the same as that in the third part, data can be imported and exported to each other. The preceding information is only prompted in Chinese or English.

1.1.2 query the character set of Oracle

When importing data, these three character sets must be consistent: one is the character set on the El server side, the other is the character set on the oracle client side, and the other is the dmp file character set.

A. query character sets on oracle server

SQL> select userenv ('language') from dual;

The result is similar to AMERICAN_AMERICA.ZHS16GBK.

Or select * from V $ _ NLS_PARAMETERS

B. How to query the dmp file Character Set

The 2nd and 3rd bytes of the dmp file record the dmp file character set. Use UltraEdit to open a small dmp file (in hexadecimal mode) and view the content of 2nd 3rd bytes, for example, 0354. Then use the following SQL statement to find its character set:

SQL> select nls_charset_name (to_number ('20140901', 'xxxxx') from dual;

Result: ZHS16GBK

The dmp file is large, such as 2 GB or above. It can be opened slowly or completely in a text editor. You can use commands (on unix hosts ):

Cat exp. dmp | od-x | head-1 | awk '{print $2 $3}' | cut-c 3-6

Then, you can use the preceding SQL statement to obtain its character set.

C. query the character set of the oracle client

In the windows registry, the corresponding OracleHome NLS_LANG (if the configuration station is installed, there will be a total of three: ORACLE, ID0, and HOME0 ). You can also set it in the dos window, for example, set nls_lang = SIMPLIFIED CHINESE_CHINA.ZHS16GBK, which only affects the environment variables in the window;

On unix platforms, the environment variable NLS_LANG is used. $ Echo $ NLS_LANG such as AMERICAN_AMERICA.ZHS16GBK

If the check result shows that the character set on the server and client is inconsistent, change it to the same character set on the server. (We recommend that you import the data directly on the server when importing the data)

1 2 3 4 Next Page