Explore Character Set Issues in Oracle EXP/IMP processes, oracleimp

Explore Character Set Issues in Oracle EXP/IMP processes, oracleimp

1. Problem description:

Data migration between databases is a common job. EXP/IMP is a common data migration and conversion tool, because the exported files are platform-independent, therefore, cross-platform migration is most commonly used. However, in the actual operation process, it involves character sets in the source database, client, and target database. The operator is not familiar with the character set conversion process between the three, and uses the EXP/IMP command in the first place. It often reports an error during the migration process or the import is successful if no error is reported, however, there are hidden risks behind it, and garbled characters are often displayed during queries.

 

2. Solution

2.1 source database (1) → EXP client (2) → IMP client (3) → target database (4), the data must go through the above four points during the migration process, during the data flow process (the three arrows above), the character sets at both ends of the arrows must be compared in sequence. If the two arrows are the same, the data is not converted. If the two arrows are different, the data is converted. If the character sets set between two adjacent vertices are different, it must be converted three times.

 

According to the theoretical analysis above, the best setting method is that because (1) (4) the character set of the database is fixed, set the character set of the client (2) (3) are the same as (1), so that only one character set conversion occurs during (3) → (4. However, the premise is that (4) the character set must be a superset of (1. The client character set is set through the Environment Variable NLS_LANG.

Linux: export NLS_LANG=SIMPLIFIEDCHINESE_CHINA.ZHS16GBKWindows: set NLS_LANG=SIMPLIFIEDCHINESE_CHINA.ZHS16GBK

EXP exported files can be viewed using the UE tool on WINDOWS. The numbers displayed in the second row represent the character set of the file. In sqlplus, select nls_charset_name () from dual; to view the character set represented by this number.

03 03 54 45 58 50 4F 52 54 3A

Among them, 03 54 is a hexadecimal number, representing a character set. Convert it to the 10th hexadecimal format:

SQL> select to_number ('200', 'xxxx') from dual; TO_NUMBER ('000000', 'xxxx') ---------------------- 0354 query the character set SQL represented by 0354> 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 Versions later than 10 Gb provide the new migration tool EXPDP/IMPDP, which does not need to set the client character set, but is automatically recognized and fully converted by ORACLE. This is because EXPDP/IMPDP is not a complete client, and it is not exactly the same as EXP/IMP/sqlplus. It only transmits a command to oracle. oracle generates a task internally, and files can only be exported to the server, rather than exporting files to the remote end like exp/imp. However, the premise is that the character set of the target database should be the superset of the character set of the source database.

 

In fact, when using exp/imp, expdp/impdp, the target database is not necessarily a superset of the source database. The key to the problem is that the source character can find the corresponding character on the target. Before transmitting databases with different character sets, we 'd better use the csscan tool provided by oracle to check whether the two character sets can be converted.

Oracle exp/imp Character Set causes Chinese garbled characters

Where to execute exp/imp, and where is the client, you must set the environment variable before executing import/export.
WIN: set NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

UNIX: EXPORT NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

These two character sets do not have any inclusion relationships and cannot be converted directly like this
If the data volume is small and the data type is not complex, you can convert it into a text file and then put it into the target database.
We recommend two tools, sqluldr2, to search for them. They are available online and used to Output Database Text.
Sqlldr is an oracle tool used to import data into the target database.
The two tools can be used together to complete character set conversion.

After imp, Chinese characters in the table are normal and garbled characters are stored. How can this problem be solved? Same as the exp Character Set

I think it is a problem with the client character set.
When performing character conversion in Oracle, you need to check the character set settings on your client.
I remember it was the NLS_LANG variable, not the LANG you checked.
Try Setting NLS_LANG = AMERICAN_AMERICA.AL32UTF8 once
Both sides are!
I'm also curious. In principle, I don't know if it works.