Oracle Character Set Overview

How to query Oracle character sets
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.
1. query character sets of Oracle Server
There are many ways to find the character set of the oracle server. The intuitive query method is as follows:
SQL> select userenv ('language') from dual;
The result is as follows: AMERICAN _ AMERICA. ZHS16GBK. android deletes a third-party jar.
2. How to query the dmp file Character Set
The dmp file exported using Oracle's exp tool also contains character set information. The 2nd and 3rd bytes of the dmp file record the character set of the dmp file. If the dmp file is not large, for example, only a few MB or dozens of MB, you can use UltraEdit to open it (in hexadecimal mode) and view the content of 2nd 3rd bytes, such as 0354, then, use the following SQL statement to find the corresponding character set:
SQL> select nls_charset_name (to_number ('20140901', 'xxxxx') from dual; ZHS16GBK
If the dmp file is large, for example, 2 GB or above (this is also the most common case), you can use the following command (on a unix host) to open it slowly or completely ):
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.
3. query the character set of the Oracle client
This is relatively simple. In Windows, the NLS_LANG corresponding to OracleHome in the Registry can also be set in the Dos window, for example:
Set nls_lang = AMERICAN_AMERICA.ZHS16GBK
In this way, only the environment variables in this window are affected. On Unix platforms, it is the environment variable NLS_LANG.
$ Echo $ NLS_LANG AMERICAN_AMERICA.ZHS16GBK
If the check result shows that the character sets on the Server and Client are inconsistent, change them to the same character set on the Server.
Modify the character set of Oracle
As mentioned above, oracle character sets have an inclusive relationship.
For example, us7ascii is a subset of zhs16gbk. From us7ascii to zhs16gbk, there will be no data interpretation problems or data loss. Utf8 should be the largest among all character sets because it is based on unicode and stores double-byte characters (so it occupies more space ).
Once a database is created, the character set of the database cannot be changed theoretically. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to the official instructions of Oracle, the conversion of character sets from subsets to supersets is supported, but not vice versa. If there is no relationship between Subsets and supersets between the two character sets, Character Set conversion is not supported by oracle. For database server, incorrect Character Set modification may lead to many unmeasurable consequences, which may seriously affect the normal operation of the database, therefore, before modification, check whether the two character sets have the relationship between Subsets and supersets. Generally, we do not recommend that you modify the character set of the oracle database server unless you have. In particular, the two most commonly used character sets ZHS16GBK and ZHS16CGB231280 do not have a subset or superset relationship. Therefore, in theory, mutual conversion between these two character sets is not supported.
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
Modify the dmp file character set:
As mentioned above, the 2nd 3rd bytes of the dmp file records the character set information. Therefore, you can directly modify the 2nd 3rd bytes of the dmp file to 'Cheat '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. Some people on the internet use java stored procedures to write the Conversion Program (the advantage of using java stored procedures is that the versatility is good, but the disadvantage is relatively troublesome ). I passed the test in Windows. However, the JVM option must be installed for Oracle databases.

Author: "The blog of daemon"