Oracle exp/IMP Character Set

Use ultraedit32 to open the exported DMP file. The file content is displayed in hexadecimal notation. The second and third bytes of data record the character set of this file.

For example:

03 54

Convert it to a 10-digit system with a value of 852. Use the command

Select nls_charset_name (852) from dual

Expected result

Zhs16gbk

Currently, the Windows XP Simplified Chinese operating system is used. The default Character Set of the Oracle client is GBK.

Create a database and set the database character set and National Character Set to utf8. If you need to export files to this operating system and import them to another Windows XP Simplified Chinese Operating System

On the server, you must change the client character set to utf8.

Method 1: Change the DMP file 2 and 3 bytes to the ID value corresponding to the Oracle Character Set Name.

For example, run the select nls_charset_id ('utf8') from dual command. The result is 871.

Convert 871 to hexadecimal format 03 67, that is, change 03 54 to 03 67

Import the modified DMP File

Method 2: Modify the registry entry nls_lang to utf8

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/battlehawk/archive/2009/12/28/5090175.aspx

 

 

During the export operation, it is very important to set the character set of the client, that is, the client's nls_lang settings.
The nls_lang parameter consists of the following parts:
Nls_lang = <language >_< territory>. <clients characterset>
The meanings of nls_lang are as follows:
Language:
-Oracle message language
-Display month and day in date
Specified by territory
-Currency and Number Format
-Habits of regions and computing weeks and dates
Characterset:
-Control Client ApplicationsProgramCharacter set used
Usually set or equal to the client (such as Windows)CodePage
Or set the Unicode application to utf8.
Run the chcp command to view the current system code page on Windows:
E: \> chcp
Active Code Page: 936
The code page 936 is the Chinese Character Set GBK.

Generally, it is best to set the client character set to the same as that of the database during export. This prevents unnecessary data conversion during export. The exported file will have the same character set as the database.
Even if the exported file will be imported to a database of different character sets in the future, this can also delay the conversion to the import time.
When importing data, there are two main situations:
1. The source database and target database have the same character set settings
At this time, you only need to set nls_lang to be equal to the database character set to import (the premise is that the export uses the same character set as the source database, that is, the three are the same)
2. The character sets of the source and target databases are different.
If the nls_lang used for export is the same character set as the source database, you can set the client nls_lang to the character set used for export.
Sample conversion only occurs on the Database End and only occurs once.

open the DMP file with ultraedit to view the hexadecimal file. The second-to-second byte represents the character set. If it is "00 01 ".
on UNIX, run the following command: CAT expdat. DMP | OD-x | HEAD
View the hexadecimal ID in decimal format:
SQL> select to_number (1, 'xxxx') from dual;
to_number (1, 'xxxx')
-----------------
1
View the character set represented by this decimal ID:
SQL> select nls_charset_name (1) from dual;
nls_charset_name (1)
numeric
us7ascii
View the decimal ID of the target character set:
SQL> select nls_charset_id ('zhs16gbk ') from dual;
nls_charset _ ID ('zhs16gbk')
------------------------
852
View the hexadecimal format of the decimal ID:
SQL> select to_char (852, 'xxxx') from dual;
to_char (852, 'xxxxx')
enabled
354
open the DMP file with ue, changing "00 01" to "03 54" Completes Character Set conversion.

You can use the following script to query valid character sets in a database:
Col nls_charset_id for 9999
Col nls_charset_name for A30
Col hex_id for A20
Select nls_charset_id (value) nls_charset_id, value nls_charset_name, to_char (nls_charset_id (value), 'xxxx') hex_id
From v $ nls_valid_values
Where parameter = 'characterset'
Order by nls_charset_id (value );

Query the Oracle server character set:
Select * from V $ nls_database_parameters;
Query the session Character Set logged on to the client:
Select * from V $ nls_session_parameters;

There are usually three character set settings in our real environment.
1. Client application Character Set)
2. Client nls_lang parameter settings
Third, set the character set on the server.
We say that the display of a character in a client application (such as sqlplus, CMD, and notepad) depends on the operating system of the client and the characters that the client can display,
We can enter these characters in the application. Whether these characters can be stored in the database is closely related to the other two character set settings.
During transmission, the client nls_lang is mainly used for conversion judgment.
If nls_lang is equal to the database character set, the characters are directly inserted into the database without any conversion.
If the conversion is different, the conversion mainly involves two tasks.
If a ing exists, the corresponding binary encoding will be mapped (after this ing, the characters may be converted) to the database.
If there is no correspondence, a replacement character is passed (many platforms are ?)
Database character set. When the character set is different from the client nls_lang, the characters converted by nls_lang are further processed.
For? (That is, there is no correspondence between the characters) directly? Save the form to the database
For other characters, it is converted between the nls_lang and the database character set and saved to the database.