From: http://dev.tot.name/db/html/20090321/20090321223657.htm
AbstractImproper Character Set setting is a key issue that affects Chinese Character Display in Oracle databases. Based on practical experience, this article introduces Oracle's classification, composition, and setting methods of character sets, and analyzes common symptoms and causes of garbled characters in Oracle databases, and put forward effective solutions to various phenomena and causes.
Keywords Oracle Character Set garbled Solution
1 Introduction Oracle database is an industry-leading database product and has been widely used in large and medium-sized enterprises in China in recent years. Although Oracle database products are already quite mature in localization, many users still have the problem of garbled characters. For example, different users in the same database obtain different results for the username query in the same table: "Oracle ??????" And Oracle China Ltd. The Chinese characters are clearly garbled in the results. Why? Improper Character Set setting is a key issue that affects Chinese Character Display in Oracle databases. 2. Character Set is set by Oracle to adapt to text display in different languages. Character sets used for Chinese character display are zhs16cgb231280, zhs16gbk, us7ascii and UTF-8. Character sets exist on both the server side and the client side. The server character set is specified when oracle is installed. The character set registration information is stored in the V $ nls_parameters table of the Oracle database dictionary, while the client character set is in the system registry (Windows System) or set in the user's environment variable (UNIX system. 3. The composition and setting of character sets are divided into two types: client and server: (1) Composition and setting of the client character set. The character set of the client is set by the Environment Variable nls_lang of the current user. Environment Variable nls_lang composition: nls_lang = language_territory.charset where language specifies the language of the server message. Territory specifies the date of the server and the character set specified by the numeric format charset. Three components can be combined at will, for example: american_america.us. the Character Set setting method of the zhs16gbk client is slightly different for different operating systems: Windows is set in the registry key: HKEY_LOCAL_MACHINE \ SOFTWARE \ oracle \ home0 \ nls_lang; the UNIX system is set in the environment variable of the current user. For example, add a line in the profile file of the current user as follows: Code : Nls_lang = simplified chinese_china.zhs16gbk; export nls_lang (2) Composition and settings of the server character set. The server character set consists of the nls_language, nls_territory, and nls_characterset values of the data dictionary table V $ nls_parameters. The value of nls_characterset is the specific database character set. If you use the query statement SQL> select * from V $ nls_parameters; you can get the following results: parameter VALUE------------------------------------------------------------NLS_LANGUAGE simplified chinesenls_territory China ...... Nls_characterset zhs16gbk ...... That is, the character set used by the current database is zhs16gbk. The character set of the database server is set when data is created. However, you can modify the character set as follows: Method 1: rebuild the database. When you create a database, set the character set of the database to the desired character set. Method 2: Modify the SYS. Props $ table. After you log on to Oracle using sys, use the following statement to modify the corresponding character set and submit: SQL> Update props $ set value $ = 'zhs16gbk' where name = 'nls _ characterset '; SQL> commit; this method is used to change the database character set, which is only valid for the changed data. That is, the original data in the database is still stored in the original character set. In addition, some use the create database Character Set zhs16gbk command to temporarily modify the character set. After the database is restarted, the character set of the database will be restored to the original character set. 4. Common Chinese Character garbled characters and solutions to correctly display Chinese character information in the Oracle database on the client must first make the character set on the client consistent with that on the server; second, the data Character Set loaded to the Oracle database must be consistent with the server character set. According to this, the problem of garbled characters can be roughly divided into the following situations: (1) the client character set is different from the server character set, and the server character set is consistent with the loaded data character set. This is the most common case. You only need to set the character set of the client correctly. Specific solution: Step 1: Query v $ nls_parameters to get the server's Character Set: SQL> select * from V $ nls_parameters; parameter VALUE-----------------------------------------------------NLS_LANGUAGE simplified chinesenls_territory China ........................ Nls_characterset zhs16gbk ........................ Step 2: Set the character set of the client according to the character set of the server. For the setting method, see the setting method of the character set of the client. Take the UNIX system as an example. You can add the following two lines to the profile file of the current user: nls_lang = simplified chinese_china.zhs16gbk export nls_lang (2) the client character set is the same as the server character set, the server character set is inconsistent with the loaded data character set. This usually happens when the Oracle version is upgraded or the database is reinstalled with a different character set than the original database, and the recovered backup data is still detached from the original character set. Another scenario is to load data that is detached from other Oracle databases that use different character sets. In both cases, Chinese characters cannot be correctly displayed no matter whether the client character set is consistent with the server character set. Specific solution: solution 1: Modify the server-side character set in accordance with the modification method of the server-side character set and load the data character set, and then import the data. Solution 2: Use Data Format dump to avoid Character Set problems. Import the loaded data to a database consistent with the character set, and then export the data in text format (when the data volume is small ), you can either use a third-party tool (such as Power Builder, access, and FOXPRO) to output data, and finally import the data to the target database. (3) The client character set is different from the server character set. The server character set is different from the input data character set. In this case, when the client character set is inconsistent with the server character set, the Chinese character information is entered from the client. The entered information cannot display Chinese characters even if the client character set is changed correctly. Solution: Modify the client Character Set and the server character set, and then re-enter the data. 5. According to the instructions in the Oracle official documents, once a database is created, the character set of the database cannot be changed. Therefore, it is very important to consider which character set your database will use in advance. The general rule for selecting a database character set is to set the database character set to a superset of the local character set of the operating system, and the database character set should also be a superset of all customer character sets. For example, in a Chinese environment, when we select zhs16cgb231280 or zhs16gbk, we choose zhs16gbk because it contains the zhs16cgb231280 character set. Jonathan gennick Carol MCCULLOUGH-DIETER GERRIT-JAN linker, Translator: Zhao Yanqin, Liu Guanying, Qin Yujie, etc. oracle8i DBA. electronic Industry Press 2 Jason couchman, Sudheer marisetti. OCP Oracle9i Database: fundamentals I exam guide. press: MCGRAW-HILL 3 Oracle Corporation. oracle 9i database administration fundamentas I student guide
