Oracle Character Set document

An Oracle character set is a collection of symbols that are interpreted as a byte of data, having a size and a mutual containment relationship. Oracle's support for the national language architecture allows you to use localized languages to store, process, and retrieve data. It makes database Tools, error messages, sort orders, dates, times, currencies, numbers, and calendars automatically adapted to localized languages and platforms.

Oracle Character set-related knowledge

1 parameter description

The most important parameter that affects the Oracle database character set is the Nls_lang parameter

Oracle's character set naming follows the following naming rules:

<language><bit size><encoding>

namely: < language >< bit number >< code >

For example: ZHS16GBK represents the use of GBK encoding format, 16-bit (two-byte) Simplified Chinese character set

Its format is as follows: Nls_lang = Language_territory.charset

It has three components (language, region, and character set), and each component controls the characteristics of the NLS subset

Where: Language Specifies the language of the server message, territory the date and number format of the specified server, charset the specified character set. such as: American _ AMERICA. Zhs16gbk

From the composition of Nls_lang we can see that the real impact of the database character set is actually the third part,

So the character set between the two databases as long as the third part of the same can be imported to export data, before the impact of only the hint information is Chinese or English.

2 View the database character set

This involves three aspects of the character set:

One is: Oracel server end of the character set;

The second is: Oracle client side of the character set;

Three is: DMP file's character set;

When you do the data import, you need all three character sets to be consistent to import correctly.

2.1 Querying Oracle Server-side character sets

There are a number of ways to detect the character set on the Oracle server side

① a more intuitive query method

Sql> Select Userenv (' language ') from dual;

The results of the query are as follows: Simplified Chinese_china. zhs32gb18030

② View all parameters about the character set

SELECT * from V$nls_parameters;

Nls_characterset zhs32gb18030 in the results

2.2 View the character set of the DMP file

The DMP file exported with the Oracle Exp tool also contains character set information, and the 2nd and 3rd bytes of the DMP file record the character set of the DMP file. If the DMP file is small, such as only a few m or dozens of m, you can open it with UltraEdit (16), Look at 2nd 3rd byte, such as 0354, and then use the following SQL to identify its corresponding character set:

Sql> Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual; Zhs16gbk

2.3 Querying Oracle Client-side character sets

Under the Windows platform, is the corresponding oraclehome in the registry Nls_lang. Specific path:

The Win+ràregeditàhkey_local_machinesoftwareoracle Nls_lang displays the local character set, which affects only the environment variables within the window.

Under the UNIX platform, it is the environment variable Nls_lang.

$echo $NLS _lang

American_america. Zhs16gbk

If the result of the check finds that the server side is inconsistent with the client-side character set, it is recommended that the uniform be modified to the same server-side character set.

2.4 Recommendations

The character set requirements for character set clients are consistent with the server to correctly display non-ASCII characters of the database. If multiple settings exist, ALTER session> environment variable > Registry > Parameter file.

The client's character set requires consistency with the server in order to correctly display non-ASCII characters of the database. If multiple settings exist, ALTER session> environment variable > registry > Parameter File

Character sets require consistency, but language settings can be different, and language settings are recommended in English. If the character set is ZHS16GBK, then Nls_lang can be AMERICAN_AMERICA.ZHS16GBK.

2.5 Supplemental Content

2.5.1 Database Server Character Set

SELECT * from Nls_database_parameters;

From Props$, is the character set that represents the database.

2.5.2 Client Character Set environment

SELECT * from Nls_instance_parameters;

It comes from V$parameter, which indicates the setting of the client's character set, possibly a parameter file, an environment variable, or a registry

2.5.3 Session Character Set environment

SELECT * from Nls_session_parameters;

From V$nls_parameters, which represents the session's own settings, may be the environment variable for the session or the ALTER session is complete, and will be consistent with nls_instance_parameters if there are no special settings.

Ii. about Oracle Character Set modification

Once the database is created, the character set of the database is theoretically immutable. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to Oracle's official instructions, the conversion of character sets is supported from subsets to superset, not vice versa. If there is no relationship between the two character sets and the superset at all, then the conversion of the character set is not supported by Oracle. In the case of database server, the wrong modification of the character set will result in many unpredictable consequences and may seriously affect the normal operation of the database, so make sure to confirm that the two character sets have a subset and a superset before modifying them. In general, we do not recommend modifying the character set on the server side of the Oracle database unless last resort. Specifically, there is no subset and superset relationship between the two most commonly used character sets ZHS16GBK and zhs16cgb231280, so the mutual conversion between the two character sets is theoretically not supported.

The following example modifies the character set of the database from ZHS16GBK to zhs32gb18030.

1. Execute 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 annotation

View Character Set parameters: SELECT * from V$nls_parameters;

①sql_trace is an Oracle-provided tool for SQL tracing and is a powerful aid diagnostic tool. Sql_trace is a very common method in the daily diagnosis and resolution of database problems.

Sql_trace =true

Enabling Sql_trace globally causes the activities of all processes to be tracked, including background processes and all user processes, which often result in more serious performance problems, so use caution in a production environment, which is a dynamic parameter after 10g and can be adjusted at any time, and is very effective in some diagnostics.

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

② the database during use

ALTER SYSTEM DISABLE restricted session to cancel the restricted state of the database. Only users who have create session permissions on the database can connect to the database during a restricted database. This state is conducive to the database backup, recovery, import, export and other operations.

③ first set job_queue_processes=0,oracle will kill CJQ0 and the corresponding job process

The ④aq_tm_processes value can be selected from 1 to 10, and 0 represents the shutdown queue monitoring

⑤ uses the Internal_use keyword to modify the locale to allow Oracle databases to bypass the checksum of subsets and superset

⑥startup Nomount

You can then use the ALTER DATABASE mount to hang the database. You can use the startup mount to start the database and mount the database, but keep the database closed. You can later open the database by using ALTER DATABASE open.