Explore character set issues in Oracle's EXP/IMP process

1. Description of the problem:

Data migration between databases is a common job, and the Exp/imp tool is a common data migration and conversion tool that is most commonly used in cross-platform migrations because of its platform-agnostic export files. But in the actual operation process, involves the source database, the client, the target database three aspects character set question. Operators of the character set conversion process between the three are not understood, and the use of exp/imp command, often in the migration process error termination, or in the case of no error, the successful import, but there is a hidden danger, in the query often show garbled.

2. Workaround

2.1 Source-side database (1) →exp Client (2) →imp client (3) → Target database (4), data in the migration process to experience the above 4 points, the data during the flow (such as the 3 arrows above) need to compare the character set at both ends of the arrow, if the same is not converted, if the difference is converted. If the set of character sets is not the same between two adjacent points, you need to convert 3 times.

According to the above theoretical analysis, the best way to set up is because (1) (4) The character set of the database is fixed, the setting client's character set (2) (3) is the same as (1), so that a maximum of (3) → (4) in the process of a character set conversion occurs. However, the premise is that the character set (4) must be a superset of the character set (1). Client character sets are set by the environment variable Nls_lang.

Linux:export Nls_lang=simplifiedchinese_china. Zhs16gbkwindows:set Nls_lang=simplifiedchinese_china. Zhs16gbk

exp Exported files can be viewed through the tool UE on Windows, where the first line of the 2nd, 3 bytes of the displayed number represents the character set of the file. in SqlplusSelect Nls_charset_name () from dual; You can view the character set represented by the number.

From 4F to 3A

Of these, 03 54 is a 16-digit number that represents a character set. Convert it to 10 binary as:

Sql> Select To_number (' 0354 ', ' xxxx ') from dual; To_number (' 0354 ', ' XXXX ')------------------------                     852 Query 852 represents the character set sql> select Nls_charset_name (852) from dual; Nls_char--------ZHS16GBK Of course can also reverse operation sql> Select nls_charset_id (' ZHS16GBK ') from dual; nls_charset_id (' ZHS16GBK ')--------------------------                       852

2.2 Oracle provides a new migration tool EXPDP/IMPDP in the release of 10g, which eliminates the need to set the client character set, but is automatically recognized by Oracle and converted to a full character set. This is because EXPDP/IMPDP is not a client in the full sense, it is not exactly the same as the exp/imp/sqlplus. It simply transmits a command to Oracle, where Oracle generates a task that can only be exported on the server and not exported to the remote end like Exp/imp. However, the premise remains that the character set of the target database should be a superset of the source database character set.

In fact, when using EXP/IMP,EXPDP/IMPDP, it is not necessary to be strict with the target database is a superset of the source database. The key to the problem is that the characters on the source side can find the corresponding characters at the destination. Before transferring a database in a different character set, it is best to check whether the two character sets can be converted between them by using the Csscan tool provided by Oracle.

Explore character set issues in Oracle's EXP/IMP process