Troubleshooting of garbled characters in the Oracle database tool

Oracle client query tools sometimes fail to display the result of investigation in Chinese text, either garbled or question mark. PLSQL has this problem, which is caused by version, the old database is the same as the latest one, and the new database tool is the same, but note that the data is actually correct and the display is normal Chinese, the question mark is displayed in the tool. In fact, the principle of the problem is very simple, that is, the incorrect Character Set settings, but such a simple principle will encounter a lot of trouble in the process of solving the problem. The following describes the process I encountered and solved, give me some ideas. Maybe you have the same problem as me. You don't have to go through this article. It's done very quickly, and it feels like it's moving ......

First of all, let's talk about character sets. Oracle character sets are a collection of symbols for the interpretation of byte data, which can be divided into sizes and inclusive. 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. The format is as follows:

Nls_lang = language_territory.charset

It has three components (language, region, and Character Set), each of which controls the NLS subset features.

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.

Many people have encountered data import failures due to different character sets. This involves three character sets: 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. During data import, the three character sets must be consistent before the data can be correctly imported.

There are many ways to query the character set of the Oracle server. The intuitive query method is as follows: SQL> select userenv ('language') from dual;

The results are as follows: American _ America. zhs16gbk

There are two possible Chinese garbled characters in the query tool result set:

1. The server and related tools have been installed. In this case, check whether the nls_lang item in the registry is correctly set due to incorrect character sets during installation;

2.Only the client is installedNo supporting tools (such as sqlplus). Take 10 Gb as an example,There is no nls_lang in the registry. So what should I change? Similarly, you still need to change the character set, but not in the registry, but inAdd or modify Environment Variables Check whether the environment variable in Windows has nls_lang. If yes, modify it. If no new one exists, set the value to the server-side character set. You can use the preceding method to query the server-side character set.

You can also modify the server-side character set hard, but it is not recommended because it may cause unexpected problems.

Modify the server character set (not recommended ). Before Oracle 8, you can directly modify the data dictionary table props $ to change the character set of the database. However, after oracle8, at least three system tables record the information of the database character set. modifying only the props $ table is incomplete and may cause serious consequences.

The correct modification method is as follows:

$ Sqlplus/nolog

SQL> Conn/As sysdba;

If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command:

SQL> startup Mount;

SQL> alter system enable restricted session;

SQL> alter system set job_queue_processes = 0;

SQL> alter system set aq_tm_processes = 0;

SQL> alter database open;

SQL> alter database character set zhs16gbk;

SQL> alter database National Character Set zhs16gbk;

SQL> shutdown immediate;

SQL> startup

Character Set matching also exists for imported and exported data files; otherwise, the data files cannot be properly displayed.

Modify the DMP file Character Set

The 2nd byte of the DMP file records the character set information. Therefore, you can directly modify the 3rd byte content of the DMP file to 'foo' the Oracle check. In theory, this can be modified only from the subset to the superset, but in many cases it can be modified without the subset and superset relationships. Some of our commonly used character sets, such as us7ascii, we8iso8859p1, zhs16cgb231280, and zhs16gbk can be modified. Because only the DMP file is changed, it has little impact.

There are many specific modification methods. The simplest is to directly use ultraedit to modify the 2nd and 3rd bytes of the DMP file. For example, if you want to change the DMP file's character set to zhs16gbk, you can use the following SQL statement to find the hexadecimal code corresponding to this character set:

SQL> select to_char (nls_charset_id ('zhs16gbk'), 'xxx') from dual;

0354

Modify the 2 and 3 bytes of the DMP file to 0354.

If the DMP file is large and cannot be opened with ue, you need to use the program method.

At this point, even if the problem of Garbled text is basically finished, you can refer to your own situation to modify it and it should be solved!