View and modify character sets of oracle servers and clients

View and modify the character set of the oracle server and client. 1. What is the oracle character set? The Oracle character set is a collection of symbols for the interpretation of the byte data. It can be divided into different sizes and have an inclusive relationship with each other. 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, the composition of AMERICAN _ AMERICA. ZHS16GBK from NLS_LANG shows that the true 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. 2. Check the database character set. This involves three character sets: character set on the El server side, character set on the oracle client side, and character set on the dmp file. During data import, the three character sets must be consistent before the data can be correctly imported. 1. There are many ways to query the character set of the oracle server 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 2. How to query the dmp file character set the dmp file exported by oracle exp tool also contains the character set information. The 2nd and 3rd bytes of the dmp file record the dmp file character set. 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 out the corresponding character set: SQL> select nls_charset_name (to_number ('000000', 'xxxx') from dual; ZHS16GBK if the dmp file is large, for example, if there is more than 2 GB (which is the most common case), you can use the following command (on a unix host) to open the file slowly or completely but cannot open it: cat exp. dmp | od-x | head-1 | awk '{print $2 $3}' | cut-c 3-6 then use the preceding SQL statement to obtain its character set. 3. It is relatively simple to query the character set of the oracle client. On windows, it is the NLS_LANG of OracleHome in the registry. You can also set it in the dos window. For example, set nls_lang = AMERICAN_AMERICA.ZHS16GBK only affects the environment variables in the window. On unix platforms, the environment variable NLS_LANG is used. $ Echo $ NLS_LANG AMERICAN_AMERICA.ZHS16GBK. Supplement: (1). select * from nls_database_parameters from props $ indicates the character set of the database. (2 ). client Character Set environment select * from nls_instance_parameters it comes from v $ parameter, indicating the character set setting of the client, which may be a parameter file, environment variable or registry (3 ). select * from nls_session_parameters in the session Character Set environment is from v $ nls_parameters, which indicates the session's own settings. It may be the session environment variable or the alter session is completed. If the session has no special settings, it will be consistent with nls_instance_parameters. (4). Only when the character set of the client must be the same as that of the server can the non-Ascii characters of the database be correctly displayed. If multiple settings exist, the character set of alter session> environment variable> registry> parameter files must be consistent, but the language settings can be different. We recommend that you use English for language settings. If the character set is zhs16gbk, The nls_lang can be American_America.zhs16gbk. Iii. Modifying oracle character sets 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, Character Set conversion is from a subset to a superset, 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 servers, incorrect Character Set modification may lead to many unpredictable 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. 1. 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> S TARTUP Note: If there are no large objects, there is no impact on language conversion during use (remember to set the character set must be supported by ORACLE, or you cannot start it, however, there may be a message like 'ora-12717: Cannot alter database national character set when NCLOB data exists'. either of the two methods is to solve this problem, use the INTERNAL_USE keyword to modify the region settings. Another option is to use re-create, but re-create is a bit complicated. Therefore, use internal_use, SQL> shutdown immediate; SQL> STARTUP MOUNT EXCLUSIVE; SQL> alter system enable restricted session; SQL> ALTER SYSTEM SET JOB_QUEUE_PRO CESSES = 0; SQL> alter system set AQ_TM_PROCESSES = 0; SQL> alter database open; SQL> alter database national character set INTERNAL_USE UTF8; SQL> SHUTDOWN immediate; SQL> startup; if the above method is used, there is no problem with the region settings of the National charset. 2. Modify the dmp file character set. As mentioned above, the 2nd-byte 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 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'), 'xx ') from dual; 0354 then 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.