Query and modify Oracle Character Set settings

 

This article mainly discusses the following parts: how to view and query oracle character sets, modify and set character sets, and common oracle utf8 and oracle exp character sets.

1. What is the Oracle character set?

The Oracle character set is a collection of symbols for the interpretation of byte data. It can be divided into different sizes and have an inclusive relationship. 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, which affects 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: 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.

How to view the database version

Select * from v $ version contains version information, core version information, and number of digits (32-bit or 64-bit). You can view the number of digits on the Linux/unix platform through file, for example, file $ ORACLE_HOME/bin/oracle

 

Ii. view database character sets

Database Server Character Set select * from nls_database_parameters, which is derived from props $ and represents the character set of the database.

　　

Client Character Set environment select * from nls_instance_parameters, which is from v $ parameter,

　　

Indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.

　　

Select * from nls_session_parameters in the session Character Set environment, which is derived from v $ nls_parameters, indicating the session's own settings, which may be the session environment variable or the session is completed by alter session. If the session has no special settings, it will be consistent with nls_instance_parameters.

　　

The character set of the client must be the same as that of the server to correctly display non-Ascii characters of the database. If multiple settings exist, alter session> environment variable> registry> parameter file

　　

The character set 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.

 

Involves three character sets,

1. Character Set on the El server side;

2. Character Set of oracle client;

3. dmp file character set.

 

During data import, the three character sets must be consistent before the data can be correctly imported.

 

2.1 query character sets on 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;

USERENV ('language ')

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

SQL> select userenv ('language') from dual;

AMERICAN _ AMERICA. ZHS16GBK

 

2.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.

 

2.3 query character sets of 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

In this way, only the environment variables in this window are affected.

 

On unix platforms, the environment variable NLS_LANG is used.

$ 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.

 

Supplement:

(1). Database Server Character Set

Select * from nls_database_parameters

The source is props $, which indicates the character set of the database.

 

(2). Client Character Set Environment

Select * from nls_instance_parameters

It is derived from v $ parameter, which indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.

 

(3). Session Character Set Environment

Select * from nls_session_parameters

The source is v $ nls_parameters, which indicates the session's own settings. It may be the session's 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> default database Parameters

 

The character set 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.

 

 

3. Modify the character set of oracle

For 8i or later versions, you can use alter database to modify the character set, but only the subset to the superset. We do not recommend that you modify the props $ table, which may cause serious errors.

　　

Startup nomount;

Alter database mount exclusive;

Alter system enable restricted session;

Alter system set job_queue_process = 0;

Alter database open;

Alter database character set zhs16gbk;

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 the database

SQL> SHUTDOWN IMMEDIATE

 

2. Start to Mount

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;

-- From parent set to subset

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;

SQL> ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16;

-- If it is from the subset to the parent set, use the INTERNAL_USE parameter to skip the supersubset detection.

SQL> ALTER DATABASE CHARACTER 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 a message such as 'ora-12717: Cannot alter database national character set when NCLOB data exists' appears,

There are two ways to solve this problem

1. Use the INTERNAL_USE keyword to modify the region settings,

2. Use re-create, but re-create is a little complicated, so use internal_use

 

SQL> 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 NATIONAL CHARACTER SET INTERNAL_USE UTF8;

SQL> SHUTDOWN immediate;

SQL> startup;

If you follow the above steps, the National charset region settings will be fine.

 

3.2 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 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

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) Windows

Edit Registry

Regedit.exe --- HKEY_LOCAL_MACHINE --- SOFTWARE --- ORACLE-HOME

Or set in the window:

Set nls_lang = AMERICAN_AMERICA.ZHS16GBK

4. Knowledge about character sets:

4.1 Character Set

In essence, according to a certain character encoding scheme, assign a specific set of symbols to different numerical encoding sets. The earliest supported encoding scheme of Oracle Database is US7ASCII.

The character set naming rules of Oracle follow the following naming rules:

<Language> <bit size> <encoding>

Namely: <language> <bit number> <encoding>

For example, ZHS16GBK indicates that the GBK encoding format and 16-bit (two-byte) simplified Chinese character set are used.

 

4.2 character encoding scheme

 

4.2.1 single-byte encoding

(1) single-byte 7-bit character set, which can be 128 characters. The most common character set is US7ASCII.

(2) single-byte 8-bit character set, which can be defined as 256 characters, suitable for most European countries

Example: WE8ISO8859P1 (Western Europe, 8-bit, ISO standard 8859P1 encoding)

 

4.2.2 multi-byte encoding

(1) variable-length multi-byte encoding

Some characters are represented by one byte. Other characters are represented by two or more characters. Long-length multi-byte encoding is commonly used for Asian languages, such as Japanese, Chinese, and Hindi.

For example, AL32UTF8 (where AL stands for ALL, which applies to ALL languages), zhs16cgb231280

(2) fixed length multi-byte encoding

Each character uses a fixed-length multi-byte encoding scheme. Currently, the only fixed-length multi-byte encoding supported by oracle is AF16UTF16, which is only used for national character sets.

4.2.3 unicode encoding

Unicode is a single encoding scheme that covers all the known characters currently used around the world, that is, Unicode provides a unique encoding for each character. UTF-16 is a unicode 16-bit encoding method, a fixed length multi-byte encoding, with 2 bytes representing a unicode character, AF16UTF16 is the UTF-16 encoding character set.

UTF-8 is unicode 8-bit encoding, is a variable-length multi-byte encoding, this encoding can use 1, 2, 3 bytes to represent a unicode character, AL32UTF8, UTF8 and UTFE are UTF-8 encoded character sets

 

4.3 character set super

When the encoding value of A character set (character set A) contains the encoding value of all other character sets (Character Set B), and the same encoding value of the two character sets represents the same character, character Set A is the Super character of Character Set B, or Character Set B is the subset of Character Set.

In the official documents of Oracle8i and oracle9i, the subset-superset pairs table is provided. For example, WE8ISO8859P1 is a subset of WE8MSWIN1252. Because US7ASCII is the earliest Oracle Database encoding format, many character sets are supersets of US7ASCII. For example, WE8ISO8859P1, ZHS16CGB231280, and ZHS16GBK are US7ASCII supersets.

 

4.4 database character set (oracle Server Character Set)

The database character set is specified during database creation and cannot be changed after database creation. When creating a database, you can specify the character set and national character set ).

 

4.4.1 Character Set

(1) used to store CHAR, VARCHAR2, CLOB, LONG, and other data types

(2) used to mark table names, column names, PL/SQL variables, etc.

(3) used to store SQL and PL/SQL program units

 

4.4.2 National Character Set:

(1) used to store NCHAR, NVARCHAR2, NCLOB, and other data types

(2) The National Character Set is essentially an additional character set selected for oracle. It is mainly used to enhance the character processing capability of oracle, the NCHAR data type can support the use of fixed-length multi-byte encoding in Asia, while the database character set cannot. The National Character Set is redefined in oracle9i and can only be selected from AF16UTF16 and UTF8 in unicode encoding. The default value is AF16UTF16.

 

4.4.3 query character set parameters

You can query the following data dictionaries or views to view Character Set settings.

Nls_database_parameters, props $, v $ nls_parameters

In the query results, NLS_CHARACTERSET indicates the character set, and NLS_NCHAR_CHARACTERSET indicates the national character set.

 

4.4.4 modifying database character sets

As mentioned above, the database character set cannot be changed in principle after it is created. However, there are two feasible methods.

 

1. If you need to modify the character set, 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.

 

4.5 client character set (NLS_LANG parameter)

 

4.5.1 client Character Set meaning

The client character set defines the encoding method of client character data. Any character data sent from or to the client is encoded using the character set defined by the client. The client can be seen as a variety of applications that can be directly connected to the database, for example, sqlplus, exp/imp. The client character set is set by setting the NLS_LANG parameter.

 

4.5.2 NLS_LANG parameter format

NLS_LANG = <language >_< territory>. <client character set>

Language: displays the oracle message, validation, and date name.

Territory: Specifies the default date, number, currency, and other formats

Client character set: Specifies the character set that the Client will use

Example: NLS_LANG = AMERICAN_AMERICA.US7ASCII

AMERICAN is the language, AMERICA is the region, and US7ASCII is the client Character Set

 

4.5.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) Windows

Edit Registry

Regedit.exe --- HKEY_LOCAL_MACHINE --- SOFTWARE --- ORACLE-HOME

 

4.5.4 NLS parameter query

Oracle provides several NLS parameter customization databases and user machines to adapt to local formats, such as NLS_LANGUAGE, NLS_DATE_FORMAT, and NLS_CALENDER. You can query the following data dictionary or view in v $ view.

NLS_DATABASE_PARAMETERS: displays the current NLS parameter values of the database, including the database character set values

NLS_SESSION_PARAMETERS: displays the parameters set by NLS_LANG or the value of the parameters changed by alter session (excluding the client Character Set set by NLS_LANG)

NLS_INSTANCE_PARAMETE: displays the parameters defined by the parameter file init <SID>. ora.

V $ NLS_PARAMETERS: displays the current NLS parameter values of the database.

 

4.5.5 modify NLS Parameters

You can modify the NLS parameters using the following methods:

(1) modify the initialization parameter file used for instance startup

(2) modify the environment variable NLS_LANG

(3) Use the alter session Statement to modify

(4) use some SQL Functions

NLS priority: SQL function> alter session> environment variables or registry> parameter files> default database Parameters

 

V. EXP/IMP and Character Set

5.1 EXP/IMP

Export and Import are a pair of Oracle Data Reading and Writing Tools. Export outputs data from the Oracle database to the operating system file. Import reads data from these files to the Oracle database. Because exp/imp is used for data migration, character sets are involved in the process from the source database to the target database. If the character sets of these four links are inconsistent, character sets will be converted.

EXP

__________________________________________

| Imp import file | <-| environment variable NLS_LANG | <-| database character set |

------------------------------------------

IMP

__________________________________________

| Imp import file |-> | environment variable NLS_LANG |-> | database character set |

------------------------------------------

 

 

The four character sets are

(1) source database Character Set

(2) user session character set during the Export process (set through NLS_LANG)

(3) user session character set during Import (set through NLS_LANG)

(4) target database Character Set

 

5.2 export the conversion process

During the Export process, if the source database character set is inconsistent with the Export user session character set, the character set conversion will occur, and the Export user session Character Set ID will be stored in several bytes in the header of the exported file. Data may be lost during this conversion process.

 

For example, if the source database uses ZHS16GBK and the Export user session Character Set uses US7ASCII, because ZHS16GBK is a 16-bit Character Set and US7ASCII is a 7-bit character set, chinese characters cannot find equivalent characters in US7ASCII, so all Chinese characters will be lost and become "?" In this way, the generated Dmp file has been lost.

Therefore, if you want to Export the source database data correctly, the user session character set during the Export process should be equal to the source database character set or the superset of the source database Character Set

 

5.3 import conversion process

(1) determine the environment for exporting database character sets

You can get the character set settings of the exported file by reading the exported file header.

(2) determine the character set of the imported session, that is, the NLS_LANG environment variable used to import the Session.

(3) IMP reads the exported file

Read the Character Set ID of the exported file and compare it with the NLS_LANG of the import process.

(4) If the exported file character set is the same as the imported Session character set, the conversion is not required in this step. If the conversion is different, the data must be converted to the character set used for the imported Session. It can be seen that two character set conversion occurs during the process of importing data to the database.

 

First time: the conversion between the character set of the imported file and the character set used by the imported Session. If the conversion process cannot be completed correctly, the Import process to the target database cannot be completed.

Second: import the conversion between the Session Character Set and the database character set.

 

 

From the newhappy Column