转imp导入数据到ORACLE遭遇ORA-12899错误

最新推荐文章于 2022-12-31 10:31:57 发布

原创最新推荐文章于 2022-12-31 10:31:57 发布 · 394 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

oracle 数据库专栏收录该内容

0 篇文章

订阅专栏

本文介绍了解决Oracle数据库在导入导出过程中遇到的字符集问题的方法，特别是从ZHS16GBK字符集的源数据库导入到UTF-8字符集的目标数据库时出现的错误，并给出了详细的解决步骤。

错误信息：

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "JRGAZX"."DTLMB"."CC" (actual: 66, maximum: 50)

初步断定是字符集问题，中文在UTF-8里占3个字节，ZHS16GBK里占2个字节,而源dmp文件字符集是ZHS16GBK的库到出来的，现在要导入到目标字符集为UFT-8的库里。

修改oracle 10g的字符集
Oracle
修改数据库字符集为：ZHS16GBK
查看服务器端字符集SQL > select * from V$NLS_PARAMETERS
修改：$sqlplus /nolog
SQL>conn / as sysdba

SQL>SHUTDOWN IMMEDIATE
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 ZHS16GBK ;
ALTER DATABASE CHARACTER SET ZHS16GBK
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

报字符集不兼容，此时下INTERNAL_USE指令不对字符集超集进行检查：

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP

再次用imp导入，就没有问题了。

oracle@server$ imp jrgazx/jrgazx file=/opt/jrgazx1/jrgazx1.dmp fromuser=jrgazx TABLES=CWFXZB,CXQK,DQBGYYPLSJB,DQTZSM,DSYP,DTLMB

Import: Release 10.2.0.1.0 - Production on Wed May 27 01:22:54 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing JRGAZX's objects into JRGAZX
. importing JRGAZX's objects into JRGAZX
. . importing table "CWFXZB" 57197 rows imported
. . importing table "CXQK" 8471 rows imported
. . importing table "DQBGYYPLSJB" 30593 rows imported
. . importing table "DQTZSM" 11173 rows imported
. . importing table "DSYP" 4906 rows imported
. . importing table "DTLMB" 390372 rows imported
Import terminated successfully without warnings.