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 oracleutf8 and oracleexp character sets.

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.

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.