Oracle Character Set Overview

One, what is the Oracle character set

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.

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

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.

which

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.

Two. View the database character set

This involves three aspects of the character set,

One is the character set of the Oracel server end;

The second is the character set of Oracle client side;

The third is the character set of the DMP file.

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

1, query Oracle server-side character set

There are a number of ways to detect the character set on the Oracle server side, and the more intuitive query method is the following:

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

The results are similar to the following: American _ AMERICA. Zhs16gbk

2. How to query the character set of 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

If the DMP file is large, such as more than 2G (which is the most common case), it is slow to open with a text editor, and can be opened completely, using the following command (on a UNIX host):

Cat Exp.dmp |od-x|head-1|awk ' {print $ $} ' |cut-c 3-6

You can then use the above SQL to get its corresponding character set.

3, query Oracle client-side character set

This is relatively simple.

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45471.htm

Under the Windows platform, is the corresponding oraclehome in the registry Nls_lang. You can also set your own in the DOS window, for example: Set Nls_lang=american_america. Zhs16gbk

This only affects 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, unify the modifications to the same set of characters as the server side.

Add:

(1). Database server Character Set

SELECT * FROM Nls_database_parameters

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

20:53:10 sql> SELECT * from Nls_database_parameters;

PARAMETER VALUE

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

Nls_language American

Nls_territory AMERICA

Nls_currency $

Nls_iso_currency AMERICA

Nls_numeric_characters.,

Nls_characterset WE8ISO8859P1

Nls_calendar Gregorian

Nls_date_format DD-MON-RR

Nls_date_language American

Nls_sort BINARY

Nls_time_format HH.MI. Ssxff AM

Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM

Nls_time_tz_format HH.MI. Ssxff AM TZR

Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR

Nls_dual_currency $

Nls_comp BINARY

Nls_length_semantics BYTE

PARAMETER VALUE

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

NLS_NCHAR_CONV_EXCP FALSE

Nls_nchar_characterset AL16UTF16

Nls_rdbms_version 10.2.0.1.0

Rows selected.

(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

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

(4). 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.

Iii. modifying the character set of Oracle

As mentioned above, Oracle's character set has a mutual containment relationship. If Us7ascii is a subset of ZHS16GBK, from Us7ascii to ZHS16GBK there will be no data interpretation problems, no data loss. UTF8 should be the largest in all character sets because it is based on Unicode, and Double-byte saves characters (and therefore consumes more in storage space).

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.

1. Modify server-side character set (not recommended)

Before Oracle 8, you can change the character set of a database by props$ the data dictionary table directly. However, after Oracle8, at least three system tables record the information of the database character set, only to change the props$ table is not complete, can cause serious consequences. The correct way to modify this is as follows: