Shulou(Shulou.com)06/01 Report--

The IMP import and export functions of Oracle are often used in the production environment to achieve the purpose of data migration. The character set is usually queried in the source and target databases.

The import IMP import error message in the test environment is as follows:

The import commands are 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, 2011, 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-prompts for 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-- prompts various garbled problems

Column 1 10

Column 2

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

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

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 τ

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

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

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

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

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 τ

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 τ

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 τ

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 (when knowing the source database and the 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-here is the display character set

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: if you don't know what the character set is in dmp, how to view it

1 View the medium character set of the dmp file

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

The result is: 0345

2 then use the following SQL to find its corresponding character set:

SQL > select nls_charset_name (to_number ('0354)) from dual

NLS_CHARSET_NAME (TO_NUMBER ('0354)

-

ZHS16GBK

How to modify a character set

There are several ways to modify the character set

Method 1. Modify the character set settings in the environment variables on the server side (server side import only needs to configure the server side, and client side import also sets the client character set). Note: this method will 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 side, so use method 1

1. Query the current environment variables

[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= "[`whoami` @ `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:/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 post-configuration 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= "[`whoami` @ `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:/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 has changed after modification.

Select * from nls_database_parameters

5. Restart the imported 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, 2011, 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...

The import of Import terminated successfully without warnings.-- is quite successful at this time, and no error has been reported.

Knowledge extension reading

During the test, the native oracle installation uses the utf8 character set, while the requirement of the project is the gbk character set. In order to prevent the import and export of different character set data information in the future, organize the following documents.

Modified oracle character set newly installed oracle, installed in AL32UTF8 format, but a project export package is ZHS16GBK format, think of ways to convert, the following is to learn

What is the oracle character set

The Oracle character set is a set of symbols for the interpretation of byte data, which can be divided into sizes and contain each other. ORACLE's architecture that supports national languages allows you to use localized languages to store, process, and retrieve data. It automatically adapts database tools, error messages, sort order, dates, times, currencies, numbers, and calendars to localized languages and platforms.

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

NLS_LANG = language_territory.charset

It has three components (language, region, and character set), each of which controls the characteristics of the NLS subset. 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 it is the third part that really affects the database character set. So the character sets between the two databases can import and export data to each other as long as the third part is the same, and the previous one only affects whether the message is in Chinese or English.

2. How to query the character set of Oracle

Many people have encountered the failure of data import due to different character sets. This involves three aspects of the character set, one is the oracel server character set, the second is the oracle client character set, and the third is the dmp file character set. When doing data import, all three character sets need to be consistent in order to import correctly. Start-run-sqlplus, enter user name: system as sysdba

Password: XXXX

1. Query the character set of oracle server

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

The results are similar as follows: AMERICAN _ AMERICA. ZHS16GBK (native result SIMPLIFIED CHINESE_CHINA.AL32UTF8)

2. How to query the character set of dmp files

The dmp file exported with oracle's exp tool also contains the character set information, and the second and third bytes of the dmp file record the character set of the dmp file. If the dmp file is small, such as only a few megabytes or dozens of megabytes, you can open it with UltraEdit (hexadecimal), look at the contents of the second and third bytes, such as 0354, and then use the following SQL to find its corresponding character set:

SQL > select nls_charset_name (to_number ('0354)) from dual

ZHS16GBK

If the dmp file is large, such as more than 2G (which is also the most common case), and it is slow or impossible to open it with a text editor, you can use the following command (on the unix host):

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

Then using the above SQL, you can also get its corresponding character set.

3. Query the character set of oracle client

This is relatively simple. Under the windows platform, it is HKEY_LOCAL_MACHINE\ SOFTWARE\ ORACLE\ HOME0\ NLS_LANG in the registry. You can also set it yourself in the dos window, such as:

Set nls_lang=AMERICAN_AMERICA.ZHS16GBK

This only affects the environment variables in this window.

Under the unix platform, it is the environment variable NLS_LANG.

$echo $NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

If the result of the check finds that the character set on the server side is not consistent with the character set on the client side, please modify it to the same character set as the server side.

Modify the character set of oracle

As mentioned above, the character set of oracle is mutually inclusive. For example, us7ascii is a subset of zhs16gbk. From us7ascii to zhs16gbk, there will be no data interpretation problems and no data loss. Utf8 should be the largest of all character sets because it is based on unicode and holds double-byte characters (and therefore takes up more storage space).

Once the database is created, the character set of the database can not be changed in theory. Therefore, it is important to consider which character set to use at the beginning of your design and installation. According to Oracle's official statement, character set conversion from subset to superset is supported, and vice versa. If there is no subset and superset relationship between the two character sets, then character set conversion is not supported by oracle. For the database server, incorrect modification of the character set will lead to many unpredictable consequences, which may seriously affect the normal operation of the database, so be sure to confirm whether there is a subset and superset relationship between the two character sets before modification. In general, we do not recommend modifying the character set on the server side of the oracle database unless you have to. In particular, there is no subset and superset relationship between the two most commonly used character sets, ZHS16GBK and ZHS16CGB231280, so theoretically, the conversion between the two character sets is not supported.

1. Modify the server character set (not recommended)

Before oracle 8, you could change the character set of the database by directly modifying the data dictionary table props$. However, after oracle8, at least three system tables record the information of the database character set, and only changing the props$ table is not complete, which may lead to serious consequences. The correct modification method is as follows:

$sqlplus / nolog

SQL > conn / as sysdba

The above method test does not work, use scott/tiger to log in to sqlplus, then connect sys/sys as sysdba, and then enter the command

If the database server is started at this time, first execute the SHUTDOWN IMMEDIATE command to shut down the database server, and then execute 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 supersubset detection

SQL > ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK

This line does not work, there is an error after execution the ORA-00933: SQL command does not end correctly, but the previous line of execution is already in effect, and this line is not mentioned in other articles.

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP

2. Modify the character set of dmp files

As mentioned above, the second and third bytes of the dmp file record the character set information, so directly modifying the contents of the second and third bytes of the dmp file can 'deceive' the oracle check. In theory, it can only be modified from subset to superset, but in many cases, it can also be modified without the relationship between subset and superset. Some commonly used character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK, can be changed. Because the change is only the dmp file, so it has little impact.

There are many specific modification methods, the simplest of which is to modify the second and third bytes of the dmp file directly with UltraEdit. For example, if you want to change the character set of a dmp file to ZHS16GBK, you can use the following SQL to find the hexadecimal code corresponding to that character set:

SQL > select to_char (nls_charset_id ('ZHS16GBK'),' xxxx') from dual

0354

Then change the 2 or 3 bytes of the dmp file to 0354.

If the dmp file is too large to be opened with ue, you need to use the program method. Someone on the Internet wrote the conversion program with java stored procedure (the advantage of using java stored procedure is that the generality is good, but the disadvantage is that it is more troublesome). I passed the test under windows. However, the oracle database is required to have the JVM option installed. Interested friends can study the program code.

Operation to change the character set encoding of ORACLE in the registry: regedit

Registry path: HKEY_LOCAL_MACHINE\ SOFTWARE\ ORACLE\ HOME0

Change the value of NLS_LANG from SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Change to AMERICAN_AMERICA.US7ASCII american_america.we8dec

PS:

Oracle UTF8 coding problem

In fact, this is an old problem, which has been dealt with many times, but has not been remembered each time, and this decision has been written down. Actually, it's very simple.

1) Oracle is actually very good, it automatically handles the conversion of the code.

2) it would be nice for the client (the machine that enters the data) to ensure that the self-knot data will not make mistakes.

On this basis, to use Oracle and store data in UTF8 (in most cases to deal with multiple languages), you only need to do two things:

A) the encoding of the instance of Oracle database is set to UTF8; (NLS_LANG=AMERICAN _ AMERICA.UTF8)

B) encode what the data file is, encode the client settings (for example, NLS_LANG=AMERICAN _ AMERICA.ZHS16GBK is modified in the registry), and then import the data with the tool.

It's fine-- in the Java program, you can use UTF8 to display many languages such as China, Japan and South Korea.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.