Oracle Character Set documentation

Oracle Character SetIt is a collection of symbols for the interpretation of byte data, which 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 character set document in the oracle database is described in detail below. I hope the content described below will be helpful to you.

I. Oracle Character Set knowledge

1 parameter description'

The most important parameter that affects the character set of oracle databases is the NLS_LANG parameter.

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 bytes are used.

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

2. view database character sets

This involves three character sets:

1. Character Set of the kernel El server;

The second is the character set of the oracle client;

Third, the 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 on the oracle server

① Intuitive query method}

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

The query result is as follows: SIMPLIFIED CHINESE_CHINA.ZHS32GB18030

② View all parameters of the character set:

SELECT * FROM v $ nls_parameters;

NLS_CHARACTERSET ZHS32GB18030 In the result

2.2 view 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

2.3 query character sets of oracle client

On windows, it is the NLS_LANG of OracleHome in the registry. Specific path:

Win + rà regedit à HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ nls_lang displays the local character set, which only affects the environment variables in the window.

On unix platforms, the environment variable NLS_LANG is used.

$ Echo $ NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

If the check result shows that the character set on the server and client is inconsistent, we recommend that you change it to the same character set on the server.

2.4 Recommended s #40 <

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

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.

2.5 supplementary content

2.5.1 Database Server Character Set

SELECT * FROM nls_database_parameters;

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

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

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

Ii. oracle Character Set Modification

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.

In the following example, modify the character set of the database from ZHS16GBK to ZHS32GB18030.

1. Execute the script

For the meaning of each SQL script, see note ① ~ ⑥

Startup mount;

Alter session set SQL _trace = true;

Alter system enable restricted session;

Alter system set job_queue_processes = 0;

Alter system set aq_tm_processes = 0;

Alter database open;

Set linesize 120;

Alter database character set INTERNAL_USE ZHS32GB18030;

Shutdown immediate;

Startup;

2. related parameter comments

View character set parameters: SELECT * FROM v $ nls_parameters;

① SQL _TRACE is a powerful auxiliary diagnostic tool provided by Oracle for SQL tracking. SQL _TRACE is a very common method in daily database problem diagnosis and solution.

SQL _trace = true = 3 T \ pd 3

Enabling SQL _TRACE globally will cause activities of all processes to be tracked, including background processes and all user processes. This usually leads to serious performance problems, so be careful when using SQL _TRACE in the production environment, this parameter is a dynamic parameter after 10 GB and can be adjusted at any time, which is very effective in some diagnoses.

Tip: by enabling SQL _trace globally, we can track the activities of all background processes, abstract descriptions in many documents, and track real-time file changes, we can clearly see the close coordination between processes.

② Restrict the database to HyGqw during use

Alter system disable restricted session to cancel the RESTRICTED State of the database. When a database is restricted, only users with the create session permission on the database can connect to the database. This status facilitates database backup, recovery, import, export, and other operations.

③ Set JOB_QUEUE_PROCESSES to 0 first, and Oracle will kill CJQ0 and the corresponding job Process

④ The aq_tm_processes value ranges from 1 to 10. 0 indicates that the queue monitoring is disabled.

⑤ Use the INTERNAL_USE keyword to modify the region settings, so that the Oracle database can bypass the validation of the subset and superset)

⑥ STARTUP NOMOUNT

Then, you can use alter database mount to MOUNT the DATABASE. You can use: startup mount to start the database and MOUNT it to the database, but keep the database closed. Later, you can use alter database open to OPEN the DATABASE.

The oracle database character set is explained here. The content in this article is suitable for beginners who are just getting started. Here, we will provide a good platform for you to learn basic knowledge about oracle databases, I hope you will be able to make some gains from the content mentioned above.