About the garbled effect of the Oracle character set in the import and export of DMP files

Oracle in the DMP backup and restore, the server-side character set and the client character set will have a significant impact on the process, especially in the data table stored in Chinese, stored procedures used in Chinese encoding (note), if there is no good character set problem, when the restoration will encounter problems, There are several issues that I have encountered:

1: The Chinese characters stored in the data table after import are garbled;

2: After the import of stored procedures in the Chinese characters are garbled;

3: When importing, prompt some stored procedure does not exist, report IMP-00098 INTERNAL ERROR:IMPCCR2 Error:

Where issue 1 occurs because the character set used by the source database is inconsistent with the target database character set that you are importing now, and the target database character set is not a superset of the source database character set. The solution is to modify the character set of the target database (which is set when the database instance is created), change to the same as the source database, and then perform the import operation to solve the problem of garbled characters in the data table.

Problem 2 and Problem 3 workaround, modify the client character set, check the value of your system environment variable Nls_lang or registry hkey_local_machine-->software-->oracle under this branch to find Nls_lang key, Modify this key value and re-import the data.

Finally we need to know what the character set should be changed to?

Method One: Go to the source database to query

Required views: nls_database_parameters,props$,v$nls_parameters

Method Two: Check the prompt information in sqlplus when importing:

The last line can be seen, export client uses .... That is, the character set used by the export client is ZHS16GBK, and depending on the current settings, it is possible to convert the character set (which means it is possible to have garbled characters if the character set used now is not a superset of the exported character set). So here is the use of the Import database client character set to ZHS16GBK, and then perform the import can solve the problem.

There are some ways to modify the DMP file, personally, if the target database side character set does not meet the requirements, you can use this method to modify the DMP file, after all, the server side character set cannot be changed casually (production server under one instance may have multiple application System user data). If this is a client character set problem, it is recommended that you modify the client character set configuration, and then modify the client character set after the import is complete.