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

遭遇 ORA-01102: cannot mount database in EXCLUSIVE mode

PostgreSQL的mulitmaster replication解决方案Cybercluster

Large | Medium | Small

[

2009/05/26 23:32 | by askwan ]

错如信息：

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的库里。

Quotation

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指令不对字符集超集进行检查：

Quotation

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

Quotation

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.

---------END-----------

作者：askwan@『AskWan』
地址：http://www.askwan.com/post/174/
可以转载,转载时请以链接形式注明作者和原始出处及本声明！