Solutions to Oracle database character set Problems

---- Use ORACLE as a database management platform in large and medium-sized database management systems at home and abroad
Many. Whether it is database management capabilities or security, Oracle is beyond reproach.
The display of word information has brought a lot of trouble to Chinese users. I have been engaged in Oracle Database Management for many years.
Users and users outside China often receive support messages about Chinese Character Display issues in Oracle databases,
The main phenomenon is that Chinese characters are displayed as unidentifiable garbled characters, causing a large amount of information to be unavailable. This article will cover
The Cause and solution of this problem are discussed for users who have this problem.
Exam.
---- 1. Cause Analysis

---- Through the analysis of user feedback, it is found that improper Character Set setting affects Oracle Database Han
Key issues of word display. So how will the character set take a while? The character set is Oracle to adapt to different languages
Text display. The main character sets used for displaying Chinese characters are zhs16cgb231280,
Us7ascii, we8iso8859p1, etc. Character Set not only must exist on the server side, but also must
You have character set registration. On the server side, character set is specified when oracle is installed. Character Set registration information is stored.
In the V $ nls_parameters table of the Oracle database dictionary, the client and character set are divided into two situations:
In this case, the SQL * Net version is earlier than 2.0. the character set is Oracle. ini in the Windows System directory.
File. In another case, SQL * NET 2.0 or later (that is, 32-bit) versions, the character set is
Registered in the Windows registry. To correctly display Chinese characters in the Oracle database on the client
First, the server-side character set must be consistent with the client-side character set; second, the character set loaded to the Oracle database
The data character set must be consistent with the character set specified by the server. Therefore, we classify user problems and generate
There are roughly the following reasons for abnormal display of Chinese characters:

---- 1. 1 the character set specified by the server is different from the customer character set, and is consistent with the character set loaded.

---- This is the most common case. You only need to set the character set of the client correctly. For the solution, see
2.1.

---- The character set specified by the server is the same as the customer character set and is inconsistent with the loaded data character set.

---- This type of problem usually occurs when the Oracle version is upgraded or the system is reinstalled with the original server
Different character sets, while the recovered backup data is still detached from the original character set, and loaded from its
It uses Oracle databases of different character sets to unload data. In both cases, regardless of the server
Chinese characters cannot be displayed if the character sets of the client and client are consistent. For the solution, see 2.2.

---- 1.3 the character set specified by the server is different from the customer character set and is inconsistent with the input data character set.

---- In this case, when the character set of the client and the server is inconsistent
. The entered information cannot display Chinese characters even if the client character set is changed correctly. Solution
See Figure 2.3.

---- 2. Solution

---- The solutions for the above three situations will be given below. For ease of description, assume that the client uses
In the Windows 95/98 environment, the TCP/IP protocol has been successfully configured, and Oracle SQL * Net is installed,
SQL * pluse product.

---- 2.1 set the client character set to be consistent with the server Character Set

---- Assume that the current server uses the us7ascii character set.

---- (1) view server-side character sets

---- Log on to a valid Oracle user through SQL * Plus on the client or server, and execute the following
SQL statement:

SQL> select * from V $ nls_parameters
Parameter Value
Nls_language American
Nls_territory America
.... ....
Nls_characterset us7ascii
Nls_sort binary
Nls_nchar_characterset us7ascii

---- From the list above, we can see that the character set of the server-side Oracle database is 'us7ascii '.

---- (2) configure the client according to the server Character Set

---- There are two configuration methods:

Specify
---- When installing the Oracle client product software, select the character set consistent with the Oracle server
For example, us7ascii.

How to modify Registration Information
---- The SQL * Net version selected by the Oracle client is divided into the following two situations:

---- A. The client is SQL * NET 2.0 or earlier

---- Go to the Windows System directory, edit the Oracle. ini file, and replace the original character with us7ascii
Set, restart the computer, and the settings take effect.

---- B. The client is SQL * NET 2.0 or later.

---- Run regedit in Win98. Select HKEY_LOCAL_MACHINE as the first step, and select
Software, step 3 select Oracle, step 4 select nls_lang, key-in and Server
Character Set with the same end (in this example, american_american.us7ascii ).

---- 2.2 The forced loading data character set is consistent with the server-side character set

---- Assume that the character set for data to be loaded from the original Oracle database is us7ascii.
The server character set is we8iso8859p1.

---- Three solutions are provided below:

---- (1) reinstall oracle on the server

---- When you reinstall Oracle, select the character set consistent with the original unload data (in this example
Us7ascii ).

---- Load the original detached data.

---- This situation only applies to empty databases and data with the same character set.

---- (2) Forcibly modify the current character set of Oracle on the server

---- Before using the IMP command to load data, log on to the system DBA using SQL * Plus on the client and run
Run the following SQL statement to modify the character set of the current Oracle database:

SQL> Create Database Character Set us7ascii
* 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, so ignore it. In fact
The character set of the Oracle database has been forcibly modified to us7ascii, and then the data is loaded using the IMP command. Equal Value
After the data is loaded, shut down the database and start up the database. log on to the Oracle database as a valid user.
Database, run select * from V $ nls_parameters at the SQL> command prompt.
The character set in the Oracle database has been restored. When you view a table with Chinese character data, the Chinese character can be normalized.
It is displayed.

---- (3) Use Data Format dump to avoid character set restrictions

---- This method is mainly used to load different character sets of external Oracle databases. The method is as follows:

---- Load the data to the server with the same character set, and then use the Conversion Tool to unload
FOXBASE format or ACCESS format database, and then transfer to Oracle data of different character sets using conversion tools
In the database, this avoids Oracle Character Set troubles. Currently, there are many database format conversion tools, such
Pipeline provided by power builder5.0 or a later version, number provided by Microsoft Access database
Data Import/Export functions. For more information about the conversion method, see ..

---- 2.3 replace Chinese characters with matching character sets

---- There is no good way to match the character set between the client and the server.
After the configurations are consistent, replace the Chinese character section with the original Chinese character pattern.