Oracle Database Character Set Problem Solutions

zhaozj2021-02-16  70

---- In the large and medium-sized database management system at home and abroad, it is more than the user of Oracle as a database management platform. Oracle is unpleasable regardless of database management ability, but it is unpredictable in the display of Chinese characters. The author has brought a lot of trouble. The author has been engaged in Oracle database management, often receiving the surrounding users and field users Oracle Database Chinese Characters Display Problem Forward, the main phenomenon is to display Chinese characters as unrecognized garbled, resulting in a lot of information that cannot be used. This article will conduct some discussions on this problem and solutions, and users who have problems in this area. ---- 1, Cause Analysis ---- By analysis of the user's reflection, the setting of the set of character sets is the key issue that affects the display of the Oracle database. So what is the character set? The character set is Oracle to accommodate different words display. The character set used for Chinese characters mainly has zHS16CGB231280, US7ASCII, WE8ISO8859P1, etc. The character set is not only in the server side, but also the client must also have a character set registration. The server side, the character set is specified when installing Oracle, the character set registration information is stored in the V $ nls_parameters table of the Oracle Database Dictionary; the client, the character set is divided into two cases, and the situation is SQL * NET 2.0 or less, The character set is registered in the Oracle.ini file in the system directory of Windows; the other is SQL * NET 2.0 or more (ie 32) versions, and the character set is registered in the system registry of Windows. To properly display the Oracle Database Chinese character information correctly, first must match the character set of the server side with the client's character set; the data character set loaded to the Oracle database must be consistent with the server specified character set. Therefore, the problem exists in the classification, and the cause of the generation of Chinese characters has the following reasons: ---- 1. 1 server specified character set is different from the customer character set, and the load data character set is consistent. ---- This situation is the most common, as long as the client's character set is set correctly, the solution is shown in 2.1. ---- 1. 2 The server specifies that the character set is the same as the customer character set, which is inconsistent with the load data character set. ---- This type of problem generally occurs when the Oracle version is upgraded or reinstalled, and the character set of the original server is selected, while the restored backup data is still unloaded according to the original character set, and loaded from other Use the data of the Oracle database of different character sets. In both cases, Chinese characters cannot be displayed regardless of whether the server side and client character sets are consistent. Solution 2.2. ---- 1.3 The server specifies that the character set is different from the customer character set, which is inconsistent with the input data character set. ---- This situation is a Chinese character message from the client when the client is inconsistent with the server-side character set. This information is entered even if the client character set is changed correctly, and the Chinese characters cannot be displayed. Solution 2.3. ---- 2. Solution ---- The following will give a solution to the above three situations. For the convenience of narrative, it is assumed that the client uses the Windows95 / 98 environment and has successfully configured the TCP / IP protocol, and Oracle's SQL * NET, SQL * PLUSE product is installed. ---- 2.1 Set the client character set consistent with the server-side character set - Hypothesis The current server side uses the US7ASCIi character set.

---- (1) View server-side character set ---- SQL * Plus logged in to Oracle through the client or server-side SQL * Plus, perform the following SQL statement: SQL> SELECT * FROM VALUE NLS_PARAMETERS Parameter Value NLS_LANGUAGE AMERICAN NLS_TERRITORY America .... .... NLS_CHARACTERSET US7ASCII NLS_SORT BINARY NLS_NCHAR_CHARACTERSET US7ASCII ---- From the list information you can see the character set of the server Oracle database is 'US7ASCII'. ---- (2) Configuring the client in accordance with the server-side character set ---- Configuration method has two types: specified when installing Oracle client software ---- When you install Oracle's client product software, choose The character set consistent with the Oracle server (this example is USASCII). Method for modifying registration information ---- The version of the SQL * NET selected according to the Oracle client is divided into the following two situations: ---- a. Client is SQL * NET 2.0 below ---- Entering Windows Directory, edit the Oracle.ini file, replace the original character set with US7ASCII, restart your computer, set into force. ---- b. Client is SQL * NET 2.0 or higher ---- running regedit under Win98, first step in HKEY_MACAL_MACHINE, the second step Select Software, third step Select Oracle, fourth step Select NLS_LANG, type The same character set as the server side (this example is: American_american.us7ascii). ---- 2.2 Forced load data character sets match with server-side character set - Hally, it is necessary to load the data from the original Oracle database to US7ASCII, the current Oracle server character set is WE8ISO8859P1. ---- The following provides three solutions: ---- (1) The server-side reinstall Oracle ---- Select the character set consistent with the original unloading data when reinstalling Oracle (US7ASCII). ---- Add the data that is originally unloaded. ---- This situation is only used for empty spaces and data with the same character set. ---- (2) Forced to modify the server-side Oracle Current Character Set - Before loading the data with the Imp command, first log in to the System DBA user with SQL * Plus, perform the following SQL statement for the current Oracle database character set Modify: SQL> CREATE DATABASE Character Set US7ASCII * Create Database Character Set US7ASCII Error At Line 1: ORA-01031: Insufficient Privileges ---- You will find the above error prompt information during the execution of the statement, do not care, actually The character set of the Oracle database has been forced to modify to US7ASCII, and then load data with the IMP command.

转载请注明原文地址:https://www.9cbs.com/read-11808.html

New Post(0)