Detailed view and modification of Oracle character set _oracle

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.

Impact Oracle Database Character Set most the important parameter is Nls_langParameters.
It's formatAs 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, which indicates whether the message is Chinese or English
territory:Specifies the date and number format of the server.
Charset:Specifies the character set.
such as: American _ AMERICA. Zhs16gbk
From the composition of the 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 can import and export data to each other as long as part three. the front effect is only a hint of whether the message is Chinese or English.

Two. Related knowledge of character set:
2.1 Character Set
The essence is that according to a certain character coding scheme, a set of different numerical codes is given to a group of specific symbols. The earliest supported encoding scheme for Oracle databases is US7ASCII.
Oracle's character set naming follows 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

2.2 character encoding scheme
2.2.1 Single byte encoding
(1) Single-byte 7-bit character set, you can define 128 characters, the most commonly used character set is Us7ascii
(2) Single-byte 8-bit character set, can be defined 256 characters, suitable for most countries in Europe
For example: WE8ISO8859P1 (Western Europe, 8-bit, ISO-standard 8859P1 code)

2.2.2 Multibyte encoding
(1) Variable-length multi-byte coding
Some characters are represented in one byte, other characters in two or more characters, and variable-length multibyte encodings are often used in support of Asian languages, such as Japanese, Chinese, Hindi, etc.
For example: Al32utf8 (where Al stands for all, refers to all languages), zhs16cgb231280
(2) fixed-length multi-byte encoding
Each character uses a fixed-length byte encoding scheme, and currently Oracle's only supported fixed-length multi-byte encoding is AF16UTF16 and is used only for national character sets
2.2.3 Unicode encoding
Unicode is a single encoding scheme that covers all the known characters currently used worldwide, that is, Unicode provides a unique encoding for each character. UTF-16 is a Unicode 16-bit encoding, a fixed-length multi-byte encoding that represents a Unicode character in 2 bytes, and Af16utf16 is a UTF-16 coded character set.
UTF-8 is a Unicode 8-bit encoding, a variable-length multi-byte encoding that can represent a Unicode character in 1, 2, 3 bytes, Al32utf8,utf8, UTFE UTF-8 encoded character set

2.3 Character Set super
When the encoded value of a character set (character set a) contains the encoded value of all another character set (character set B), and the two character sets have the same encoded value representing the same character, the character set A is the super of character set B, or the character set B is a subset of the character set A.
A subset-Super table (Subset-superset pairs) is available in official documentation for Oracle8i and oracle9i, for example: WE8ISO8859P1 is a subset of we8mswin1252. Because US7ASCII is the earliest Oracle database encoding format, there are many character sets that are us7ascii, such as WE8ISO8859P1, zhs16cgb231280, and ZHS16GBK are superset of US7ASCII.

2.4 Database Character Set (Oracle server-side character set)
The database character set is specified when the database is created and cannot normally be changed after it is created. When you create a database, you can specify the character set (CHARACTER set) and the national CHARACTER set.

2.4.1 Character Set
(1) Used to store type data such as Char, VARCHAR2, CLOB, long, etc.
(2) used to mark such as table name, column name and Pl/sql variable, etc.
(3) used to store SQL and Pl/sql program units, etc.

2.4.2 National Character set:
(1) to store nchar, NVARCHAR2, NCLOB and other types of data
(2) The national character set is essentially an additional set of characters selected for Oracle, primarily for the purpose of enhancing the character processing capabilities of Oracle, since the nchar data type can provide support for the use of fixed-length multibyte encodings in Asia, while the database character set is not. The national character set is redefined in oracle9i and can only be selected in Af16utf16 and UTF8 in Unicode encoding, and the default value is Af16utf16

2.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
Nls_characterset represents the character set in the query result, Nls_nchar_characterset represents the national character set

2.4.4 Modify the database character set
As stated above, the database character set cannot be changed in principle after it is created. But there are 2 methods available.

1. If you need to modify the character set, you typically need to export the database data, rebuild the database, and then import the database data to convert it.
2. Modifying the character set through the ALTER DATABASE CHARACTER SET statement, but modifying the character set after the database is created is limited and the database character set can be modified only if the new character set is a superset of the current character set, for example, UTF8 is a us7ascii superset. Modify the database character set to use ALTER DB CHARACTER set UTF8.

2.5 client Character set (Nls_lang parameter)
2.5.1 Client Character Set meaning
The client character set defines how the client character data is encoded, and any character data originating from or destined for the client uses the client-defined character set encoding, and the client can be viewed as a variety of applications that can be directly connected to the database, such as Sqlplus,exp/imp. The client character set is set by setting the Nls_lang parameter.

2.5.2 Nls_lang parameter format
Nls_lang=<language>_<territory>.<client character set>
Language: Display Oracle messages, checksums, date naming
Territory: Specify default date, number, currency format
Client Character Set: Specifies the character set that the client will use
For example: Nls_lang=american_america. Us7ascii
American is the language, America is the region, Us7ascii is the client character set

2.5.3 Client Character Set setting method
1) UNIX Environment
$NLS _lang= "Simplified Chinese" _china.zhs16gbk
$export Nls_lang
Edit profile files for Oracle users
2) Windows environment
Edit the Registration form
Regedit.exe---"HKEY_LOCAL_MACHINE---" oracle--"SOFTWARE---"

2.5.4 NLS parameter query
Oracle provides several NLS parameters for custom databases and user machines to accommodate local formats, such as Nls_language,nls_date_format,nls_calender, which can be viewed by querying the following data dictionaries or v$ views.
Nls_database_parameters: Displays the current NLS parameter values for the database, including the database character set values
Nls_session_parameters: Displays parameters set by Nls_lang, or parameter values after alter session change (excluding client character sets 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 for the database

2.5.5 Modify NLS Parameters
Use the following methods to modify the NLS parameters
(1) Modifying the initialization parameter file used when the instance is started
(2) Modify environment variable Nls_lang
(3) using the ALTER SESSION statement to modify the
(4) using certain SQL functions
NLS Action Priority: SQL function > Alter SESSION > Environment variable or Registry > parameter file > database default parameters

Three Exp/imp and Character Set
3.1 Exp/imp
Export and Import are a pair of tools to read and write Oracle data. Exporting data from Oracle databases to operating system files, Import reads the data from these files into the Oracle database, and because of data migrations using EXP/IMP, the data from the source database to the target database has four links to the character set. Character set conversion will occur if the character sets for these four links are inconsistent.
Exp
____________ _________________ _____________
|imp import file |<-| environment variable nls_lang|<-| database character Set |
------------   -----------------   -------------
IMP
____________ _________________ _____________
|imp import file |->| environment variable nls_lang|->| database character Set |
------------   -----------------   -------------


Four character sets are
(1) Source database character Set
(2) The user session character set in the export process (set by Nls_lang)
(3) The user session character set in the import process (via Nls_lang setting)
(4) Target database character set

3.2 Exported conversion process
In the export process, character set translation occurs if the source database character set is inconsistent with the export user session character set, and the ID number of the export user session character set is stored in several bytes of the exported file's header. The loss of data may occur during this conversion process.
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, the Chinese character cannot find the equivalent characters in the US7ASCII during the conversion process. , so all Chinese characters will be lost and become "?? form so that the DMP file generated after the conversion has lost data.
Therefore, if you want to export the source database data correctly, the user session character set in the export process should be equal to the source database character set or a superset of the source database character set

3.3 Import Conversion process
(1) Determine the export database character set environment
You can get the character set settings for the exported file by reading the export file header
(2) Determine the character set to import session, that is, the NLS_LANG environment variable used by the session
(3) Imp reads the export file
Read the export file character set ID and compare the Nls_lang of the import process
(4) If the export file character set and the import session character set are the same, then there is no need for conversion in this step, and if different, you need to convert the data to the character set used in the import session. As you can see, two times character set conversion occurred during the import of data into the database
The first time: the conversion between the import file character set and the character set used by the import session, and if the conversion process does not complete correctly, import to the target database cannot be completed.
Second time: Import transitions between the session character set and the database character set.

four. View the database character set
involves three aspects of the character set,
1. Oracel server-side character set;
2. Oracle client-side character set;
3. DMP the character set of the file.

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

4.1 Querying Oracle Server-side character sets
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;
USERENV (' LANGUAGE ')
----------------------------------------------------
Simplified Chinese_china. Zhs16gbk

Sql>select userenv (' language ') from dual;
American _ AMERICA. Zhs16gbk

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

4.3 Querying Oracle client-side character sets
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.

(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.
NLS Priority level If multiple settings exist: SQL function > Alter SESSION > Environment variable or Registry > parameter file > database default parameters

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.

Five. Modifying Oracle's character set
As stated 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. 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.

However, there are 2 ways to modify the character set.
1. It is often necessary to export the database data, rebuild the database, and then import the database data in the form of conversion.
2. Modifying the character set through the ALTER DATABASE CHARACTER SET statement, but modifying the character set after the database is created is limited and the database character set can be modified only if the new character set is a superset of the current character set, for example, UTF8 is a us7ascii superset. Modify the database character set to use ALTER DB CHARACTER set UTF8.


5.1 Modifying the server-side character set (not recommended)

1. Close the database
Sql>shutdown IMMEDIATE

2. Boot 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;
--This can be from the parent set to the subset
Sql>alter DATABASE CHARACTER SET ZHS16GBK;
Sql>alter DATABASE National CHARACTER SET al16utf16;
--if it's from subset to parent set, you need to use the Internal_use parameter, skip the super subset 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 you do not have a large object, there is no effect in the use of Language conversion, (remember that the set must be Oracle support, or you cannot start), just follow the above procedure.

In the event of a hint such as ' Ora-12717:cannot ALTER DATABASE national CHARACTER SET when NCLOB data exists ',
There are two ways to solve this problem
1. Use the Internal_use keyword to modify the locale,
2. Using re-create, but re-create a bit complicated, so please 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 procedure, national CharSet has no problem with regional settings

5.2 Modifying the DMP file character set
As stated above, the 2nd 3rd byte of the DMP file records the character set information, so that directly modifying the contents of the 2nd 3rd byte of the dmp file can ' cheat ' the Oracle's check. This is theoretically only from the subset to the superset can be modified, but in many cases without subsets and superset of the case can also be modified, we often use some character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK basic can be changed. Because the change is only DMP file, so the impact is not big.

The specific modification method is more, the simplest is to modify the 2nd and 3rd bytes of the DMP file directly with UltraEdit.
For example, to change the character set of the DMP file to ZHS16GBK, you can use the following SQL to find out the 16 code corresponding to the character set: Sql> select To_char (nls_charset_id (' ZHS16GBK '), ' xxxx ') from Dual
0354
Then modify the DMP file's 2, 3 bytes to 0354.
If the DMP file is large and cannot be opened with a UE, it needs to be programmed.

5.3 Client Character Set setting method
1) UNIX Environment
$NLS _lang= "Simplified Chinese" _china.zhs16gbk
$export Nls_lang
Edit profile files for Oracle users
2) Windows environment
Edit the Registration form
Regedit.exe---"HKEY_LOCAL_MACHINE---" oracle--"SOFTWARE---"
Or in the window settings:
Set Nls_lang=american_america. Zhs16gbk