View and modify the Oracle Character Set

View and modify the Oracle Character Set 1. What is the Oracle character set? The Oracle character set is a collection of interpreted symbols of the byte data, which can be divided into 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. Www.2cto.com 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, and whether the prompt information is in Chinese or English. Territory: Specifies the date and number format of the server, 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 three character sets involved in the database character set: 1. character sets at the El server side; 2. character sets at the oracle client side; 3. character sets for dmp files. During data import, the three character sets must be consistent before the data can be correctly imported. 2.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; USERENV ('language') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBKSQL> select userenv ('language') from dual; AMERICAN _ AMERICA. the dmp file exported by oracle 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 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. 2.3 query the character set of the oracle client in windows, that is, the NLS_LANG of the corresponding 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_LANGAMERICAN_AMERICA.ZHS16GBK. Supplement: (1). Database Server Character Set select * from nls_database_parameters comes from props $, which 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, NLS takes precedence over SQL function> alter session> environment variables or registry> parameter files> database default parameter character sets must be consistent, however, 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. 3. Modifying the oracle character set as mentioned above, the database character set cannot be changed in principle after it is created. Therefore, it is important to consider which character set to use at the beginning of design and installation. 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. However, there are two ways to modify the character set. 1. You usually need to export the database data, recreate the database, and then import the database data for conversion. 2. you can use the alter database character set statement to modify the character set. However, there are limits on modifying the character set after the DATABASE is created. Only when the new character set is the current character set, the character set of the DATABASE can be modified, for example, UTF8 is a superset of US7ASCII. You can use alter database character set UTF8 to modify the character set of a DATABASE. 3.1 modify the server character set (not recommended) 1. shut down Database SQL> SHUTDOWN IMMEDIATE2. start MountSQL> 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; -- from the parent SET to the subset SQL> alter database character set ZHS16GBK; SQL> ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16; -- if it is from the subset to the parent SET, you need to use the INTERNAL_USE parameter to skip super subset detection SQL> ALTER DATABASE CHARA Cter set INTERNAL_USE AL32UTF8; SQL> alter database national character set INTERNAL_USE AL16UTF16; SQL> shutdown immediate; SQL> STARTUP Note: If there are no large objects, there is no impact on language conversion during use (remember that the character set must be supported by ORACLE, or cannot be started. If there is a message like 'ora-12717: Cannot alter database national character set when NCLOB data exists', there are two ways to solve this problem: 1. use the INTERNAL_USE keyword to modify the region settings. use re-create, but re-create is a little complicated. Therefore, use internal_useSQL> shutdown immediate; SQL> startup mount exclusive; 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 NAT Ional character set INTERNAL_USE UTF8; SQL> SHUTDOWN immediate; SQL> startup; if you follow the above practice, the National charset region settings will be fine. 3.2 modify the dmp file CHARACTER SET as mentioned above, 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 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. 3.3 client Character Set setting method 1) UNIX environment $ NLS_LANG = "simplified chinese" _ china. zhs16gbk $ export NLS_LANG edit the profile file of an oracle user 2) EDIT registry Regedit.exe --- HKEY_LOCAL_MACHINE --- SOFTWARE --- ORACLE -- HOME in Windows or set: set nls_lang = AMERICAN_AMERICA.ZHS16GBK in the window