A comprehensive analysis of character set conversion rules for Oracle database

As a user of an Oracle database, the export and import two commands are never unfamiliar, as they are the tools we often use for data backup and recovery. However, the Oracle character set problems that occur during the use of these two commands often cause unnecessary trouble and unnecessary data loss for some Oracle users. This article will summarize the rules of conversion of Oracle character set in export and import process and the precautions to use these two commands.

The reason for character set conversion

Export, import process as shown in the above figure, from this schematic can be seen around the relationship between the character set, and the inconsistency of the character set is precisely the cause of Oracle's character set conversion.

* source database character set;

* The user session character set in the export process;

* The user session character set in the import process;

* Target database Character set.

In the export and import process, Oracle character set conversion may occur if there is an inconsistency of four factors that affect character set translation, namely:

In the export process, if the source database character set is inconsistent with the export user session character set, character set conversion occurs and the ID number of the export user session character set is stored in several bytes of the exported binary format DMP file header. The loss of data may occur during this conversion process.

Example 1: If the source database uses ZHS16GBK, and the export user session character set uses US7ASCII, because ZHS16GBK is a 8-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, that is, the DMP file generated after this conversion has lost data.

Example 2: If the source database uses ZHS16GBK, the export user session character set uses zhs16cgb231280, but because the ZHS16GBK character set is a superset of the zhs16cgb231280 character set, most of the characters in the process can be converted correctly. Only some characters that exceed the zhs16cgb231280 character set become "?? Form If the source database uses the zhs16cgb231280 character set, and the export user session uses the ZHS16GBK character set, the conversion process can be completely converted to success.