When importing data from IMP in Oracle, hybriddb for MySQL prompts that the character set is inconsistent.

When importing data from IMP in Oracle, hybriddb for MySQL prompts that the character set is inconsistent.

The imp import and exp export functions of Oracle are often used in the production environment for data migration. The character set is usually queried in the source database and target database,

The following error message is displayed when importing IMP in the test environment:

The import command is as follows:

[Oracle@localhost.localdomain:/soft] $ imp scott/scott file =/soft/scott_v1.dmp log =/soft/scott_v1.log ignore = y full = y

Import: Release 11.2.0.4.0-Production on Sun Oct 22 16:26:27 2017

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT: V11.02.00 via conventional path
Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set -- indicates inconsistent character sets
. Importing SCOTT's objects into SCOTT
.. Importing table "BONUS" 0 rows imported
.. Importing table "DEPT"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_DEPT) violated-prompting various garbled issues
Column 1 10
Column 2 comment
Column 3
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_DEPT) violated
Column 1 20
Column 2 comment
Column 3±±
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_DEPT) violated
Column 1 30
Column 2 comment
Column 3
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_DEPT) violated
Column 1 40
Column 2
Column 3 0 rows imported
.. Importing table "EMP"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7369
Column 2 itpux01
Column 3 Tau
Column 4 7902
Column 5 17-DEC-1980: 00: 00: 00
Column 6 800
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7499
Column 2 itpux02
Column 3 comment
Column 4 7698
Column 5 20-FEB-1981: 00: 00: 00
Column 6 1600
Column 7 300
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7521
Column 2 itpux03
Column 3 comment
Column 4 7698
Column 5 22-FEB-1981: 00: 00: 00
Column 6 1250
Column 7 500
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7566
Column 2 itpux04
Column 3
Column 4 7839
Column 5 02-APR-1981: 00: 00: 00
Column 6 2975
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7654
Column 2 itpux05
Column 3 comment
Column 4 7698
Column 5 28-SEP-1981: 00: 00: 00
Column 6 1250
Column 7 1400
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7698
Column 2 itpux06
Column 3
Column 4 7839
Column 5 01-MAY-1981: 00: 00: 00
Column 6 2850
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7782
Column 2 itpux07
Column 3
Column 4 7839
Column 5 09-JUN-1981: 00: 00: 00
Column 6 2450
Column 7
Column 8 10
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7788
Column 2 itpux08
Column 3 ·
Column 4 7566
Column 5 19-APR-1987: 00: 00: 00
Column 6 3000
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7839
Column 2 itpux09
Column 3
Column 4
Column 5 17-NOV-1981: 00: 00: 00
Column 6 5000
Column 7
Column 8 10
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7844
Column 2 itpux10
Column 3 comment
Column 4 7698
Column 5 08-SEP-1981: 00: 00: 00
Column 6 1500
Column 7 0
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7876
Column 2 itpux11
Column 3 Tau
Column 4 7788
Column 5 23-MAY-1987: 00: 00: 00
Column 6 1100
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7900
Column 2 itpux12
Column 3 Tau
Column 4 7698
Column 5 03-DEC-1981: 00: 00: 00
Column 6 950
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7902
Column 2 itpux13
Column 3 ·
Column 4 7566
Column 5 03-DEC-1981: 00: 00: 00
Column 6 3000
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT. PK_EMP) violated
Column 1 7934
Column 2 itpux14
Column 3 Tau
Column 4 7782
Column 5 23-JAN-1982: 00: 00: 00
Column 6 1300
Column 7
Column 8 10 0 rows imported
.. Importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully with warnings.

How to query character sets

Method 1: query the table (know the source database and target database ):

Select * from nls_database_parameters;

1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CURRENCY $
4 NLS_ISO_CURRENCY AMERICA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CHARACTERSET ZHS16GBK -- the character set is displayed here.
7 NLS_CALENDAR GREGORIAN
8 NLS_DATE_FORMAT DD-MON-RR
9 NLS_DATE_LANGUAGE AMERICAN
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH. MI. SSXFF AM
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH. MI. SSXFF AM
13 NLS_TIME_TZ_FORMAT HH. MI. SSXFF AM TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH. MI. SSXFF AM TZR
15 NLS_DUAL_CURRENCY $
16 NLS_COMP BINARY
17 NLS_LENGTH_SEMANTICS BYTE
18 NLS_NCHAR_CONV_EXCP FALSE
19 NLS_NCHAR_CHARACTERSET AL16UTF16
20 NLS_RDBMS_VERSION 11.2.0.4.0

Method 2: do not know what the character set is in dmp and how to view it

1. view the character set in the dmp File

Cat '/home/soft/scott. dmp' | od-x | head-1 | awk '{print $2 $3}' | cut-c 3-6

Result: 0345

2. Use the following SQL statement to find the corresponding character set:

SQL> select nls_charset_name (to_number ('20140901', 'xxxxx') from dual;
NLS_CHARSET_NAME (TO_NUMBER ('20140901', 'xxxxx'
----------------------------------------
ZHS16GBK

How to modify character sets

You can modify character sets in several ways.

Method 1. Modify the character set settings in the environment variables on the server. (For Server Import, you only need to configure the server and client import.) Note: this method can be modified back in time after the server is modified

Method 2. Modify the character set in the database

Method 3. Modify the character set in the dmp File

 

This environment is imported on the server, so method 1 is used.

1. query the current environment variable

[Oracle@localhost.localdomain:/soft] $ cat ~ /. Bash_profile
#. Bash_profile

# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME/bin

Export PATH

# Oracle Settings oracle
TMP =/tmp; export TMP
TMPDIR = $ TMP; export TMPDIR
PS1 = "['whoam' @ 'hostname':" '$ PWD] $'
Alias sqlplus = "rlwrap sqlplus"
Alias rman = "rlwrap rman"
Alias alert = "cd/oracle/app/oracle/diag/rdbms/db01/db01/trace"
Alias lsnrctl = "rlwrap lsnrctl"
Alias adrci = "rlwrap adrci"
Export LANG = en_US
ORACLE_BASE =/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME = $ ORACLE_BASE/product/11.2.0; export ORACLE_HOME
ORACLE_SID = db01; export ORACLE_SID
ORACLE_TERM = xterm; export ORACLE_TERM

PATH =/usr/sbin: $ PATH; export PATH
PATH = $ ORACLE_HOME/bin: $ PATH; export PATH

LD_LIBRARY_PATH = $ ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH = $ ORACLE_HOME/JRE: $ ORACLE_HOME/jlib: $ ORACLE_HOME/rdbms/jlib; export CLASSPATH
NLS_DATE_FORMAT = "yyyy-mm-dd HH24: MI: SS"; export NLS_DATE_FORMAT
NLS_LANG = AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG

If [$ USER = "oracle"] | [$ USER = "grid"]; then
If [$ SHELL = "/bin/ksh"]; then
Ulimit-p 16384
Ulimit-n 65536
Else
Ulimit-u 16384-n 65536
Fi
Umask 022
Fi

 

2. Configure Environment Variables
[Oracle@localhost.localdomain:/soft] $ vi ~ /. Bash_profile

3. query the configured information

[Oracle@localhost.localdomain:/soft] $ cat ~ /. Bash_profile
#. Bash_profile

# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME/bin

Export PATH

# Oracle Settings oracle
TMP =/tmp; export TMP
TMPDIR = $ TMP; export TMPDIR
PS1 = "['whoam' @ 'hostname':" '$ PWD] $'
Alias sqlplus = "rlwrap sqlplus"
Alias rman = "rlwrap rman"
Alias alert = "cd/oracle/app/oracle/diag/rdbms/db01/db01/trace"
Alias lsnrctl = "rlwrap lsnrctl"
Alias adrci = "rlwrap adrci"
Export LANG = en_US
ORACLE_BASE =/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME = $ ORACLE_BASE/product/11.2.0; export ORACLE_HOME
ORACLE_SID = db01; export ORACLE_SID
ORACLE_TERM = xterm; export ORACLE_TERM

PATH =/usr/sbin: $ PATH; export PATH
PATH = $ ORACLE_HOME/bin: $ PATH; export PATH

LD_LIBRARY_PATH = $ ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH = $ ORACLE_HOME/JRE: $ ORACLE_HOME/jlib: $ ORACLE_HOME/rdbms/jlib; export CLASSPATH
NLS_DATE_FORMAT = "yyyy-mm-dd HH24: MI: SS"; export NLS_DATE_FORMAT
# NLS_LANG = AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG
NLS_LANG = AMERICAN_AMERICA.AL16UTF16; export NLS_LANG

If [$ USER = "oracle"] | [$ USER = "grid"]; then
If [$ SHELL = "/bin/ksh"]; then
Ulimit-p 16384
Ulimit-n 65536
Else
Ulimit-u 16384-n 65536
Fi
Umask 022
Fi

4. After configuration, you can use SQL to query whether the character set is changed after modification.

Select * from nls_database_parameters;

5. Restart the import data again.

[Oracle@localhost.localdomain:/soft] $ imp scott/scott file =/soft/scott_v1.dmp log =/soft/scott_v1.log1 ignore = y full = y

Import: Release 11.2.0.4.0-Production on Sun Oct 22 16:46:17 2017

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT: V11.02.00 via conventional path
Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. Importing SCOTT's objects into SCOTT
.. Importing table "BONUS" 0 rows imported
.. Importing table "DEPT" 4 rows imported
.. Importing table "EMP" 14 rows imported
.. Importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully without warnings. -- the Import is successful and no error is returned.

Knowledge extension reading

During the test, the local oracle installation adopts the utf8 character set, and the project requires the gbk character set. To prevent data information import and export from different character sets in the future, sort out the following documents.
The oracle character set is modified and the new oracle character set is installed in the AL32UTF8 format. However, a Project Export package is in the ZHS16GBK format. If you want to convert it, refer to the following:
1. What is the oracle character set?
The Oracle character set is a collection of symbols for the interpretation of byte data. It can be divided into different sizes and have an inclusive relationship. ORACLE supports the national language architecture, allowing you to store, process, and retrieve data in a localized language. It makes database tools, error messages, sorting order, date, time, currency, numbers, and calendar automatically adapt to localization languages and platforms.

The most important parameter that affects the character set of oracle databases is the NLS_LANG parameter. The format is as follows:

NLS_LANG = language_territory.charset

It has three components (language, region, and Character Set), each of which controls the NLS subset features. Where:

Language specifies the Language of the server message, territory specifies the date and number format of the server, and charset specifies the character set. For example: AMERICAN _ AMERICA. ZHS16GBK

From the composition of NLS_LANG, we can see that the real impact on the database character set is actually the third part. Therefore, if the character set between the two databases is the same as that in the third part, data can be imported and exported to each other. The preceding information is only prompted in Chinese or English.

Ii. How to query Oracle character sets

Many people have encountered data import failures due to different character sets. This involves three character sets: one is the character set on the El server side, the other is the character set on the oracle client side, and the other is the dmp file character set. During data import, the three character sets must be consistent before the data can be correctly imported. Start-run-sqlplus, user name input: system as sysdba
Password: XXXX

1. query character sets of oracle server

There are many ways to find out the character set of the oracle server. The intuitive query method is as follows: SQL> select userenv ('language') from dual;

The result is similar to the following: AMERICAN _ AMERICA. ZHS16GBK (the local result is SIMPLIFIED CHINESE_CHINA.AL32UTF8)

2. How to query the dmp file Character Set

The dmp file exported using oracle's exp tool also contains character set information. The 2nd and 3rd bytes of the dmp file record the character set of the dmp file. If the dmp file is not large, for example, only a few MB or dozens of MB, you can use UltraEdit to open it (in hexadecimal mode) and view the content of 2nd 3rd bytes, such as 0354, then, use the following SQL statement to find the corresponding character set:

SQL> select nls_charset_name (to_number ('20140901', 'xxxxx') from dual;

ZHS16GBK

If the dmp file is large, for example, 2 GB or above (this is also the most common case), you can use the following command (on a unix host) to open it slowly or completely ):

Cat exp. dmp | od-x | head-1 | awk '{print $2 $3}' | cut-c 3-6

Then, you can use the preceding SQL statement to obtain its character set.

3. query the character set of the oracle client

This is relatively simple. In windows, HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOME0 \ NLS_LANG in the registry. You can also set it in the dos window, for example:

Set nls_lang = AMERICAN_AMERICA.ZHS16GBK

In this way, only the environment variables in this window are affected.

On unix platforms, the environment variable NLS_LANG is used.

$ Echo $ NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

If the check result shows that the character sets on the server and client are inconsistent, change them to the same character set on the server.

3. Modify the character set of oracle

As mentioned above, oracle character sets have an inclusive relationship. For example, us7ascii is a subset of zhs16gbk. From us7ascii to zhs16gbk, there will be no data interpretation problems or data loss. Utf8 should be the largest among all character sets because it is based on unicode and stores double-byte characters (so it occupies more space ).

Once a database is created, the character set of the database cannot be changed theoretically. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to the official instructions of Oracle, Character Set conversion is from a subset to a superset, but not vice versa. If there is no relationship between Subsets and supersets between the two character sets, Character Set conversion is not supported by oracle. For database servers, incorrect Character Set modification may lead to many unpredictable consequences, which may seriously affect the normal operation of the database, therefore, before modification, check whether the two character sets have the relationship between Subsets and supersets. Generally, we do not recommend that you modify the character set of the oracle database server unless you have. In particular, the two most commonly used character sets ZHS16GBK and ZHS16CGB231280 do not have a subset or superset relationship. Therefore, in theory, mutual conversion between these two character sets is not supported.

1. Modify the server character set (not recommended)

Before oracle 8, you can directly modify the data dictionary table props $ to change the character set of the database. However, after oracle8, at least three system tables record the information of the database character set. modifying only the props $ table is incomplete and may cause serious consequences. The correct modification method is as follows:

$ Sqlplus/nolog

SQL> conn/as sysdba;

The above method cannot be tested. Use scott/tiger to log on to sqlplus, connect sys/sys as sysdba, and enter the command.


If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command:

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; // skip super subset Detection

SQL> ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK;

This line does not work, an error occurred after execution ORA-00933: the SQL command ended incorrectly, but the execution of the previous line has taken effect, other articles do not mention this line.


SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP

2. Modify the dmp file Character Set

As mentioned above, the 2nd 3rd bytes of the dmp file records the character set information. Therefore, you can directly modify the 2nd 3rd bytes of the dmp file to 'Cheat 'the oracle check. In theory, this can be modified only from the subset to the superset, but in many cases it can be modified without the subset and superset relationships. Some of our commonly used character sets, such as US7ASCII, WE8ISO8859P1, ZHS16CGB231280, and ZHS16GBK can be modified. Because only the dmp file is changed, it has little impact.

There are many specific modification methods. The simplest is to directly use UltraEdit to modify the 2nd and 3rd bytes of the dmp file. For example, if you want to change the dmp file's character set to ZHS16GBK, you can use the following SQL statement to find the hexadecimal code corresponding to this character set:

SQL> select to_char (nls_charset_id ('zhs16gbk'), 'xxx') from dual;

0354

Modify the 2 and 3 bytes of the dmp file to 0354.

If the dmp file is large and cannot be opened with ue, you need to use the program method. Some people on the internet use java stored procedures to write the Conversion Program (the advantage of using java stored procedures is that the versatility is good, but the disadvantage is relatively troublesome ). I passed the test in windows. However, the JVM option must be installed for oracle databases. If you are interested, you can study the program code.

Modify the character set encoding method of ORACLE in the Registry: regedit
Registry path: HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOME0
Extract the value of NLS_LANG from SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Changed to AMERICAN_AMERICA.US7ASCII american_america.we8dec.
PS:
Oracle UTF8 Encoding
In fact, this is an old problem. I have handled it many times and never remembered it. I decided to write it down this time. It's actually very simple,
1) Oracle is actually very good. It will automatically process the encoding conversion.
2) the client (the machine that inputs the data) ensures that the self-built data is correct without errors.
On this basis, we need to use Oracle and UTF8 (usually to cope with multiple languages) to store data. We only need to do two things:
A) set the Oracle database instance encoding to UTF8; (NLS_LANG = AMERICAN _ AMERICA. UTF8)
B) if the encoding of the data file is used, set the encoding of the client (for example, NLS_LANG = AMERICAN _ AMERICA. ZHS16GBK is modified in the Registry) and import the data using tools.
You can use UTF8 in Java programs to display Chinese, Japanese, and Korean languages.