ORA-12899錯誤—–imp匯入資料到ORACLE遭遇(修改字元集問題)

imp匯入資料到ORACLE遭遇ORA-12899錯誤

查看字元集：select * from NLS_DATABASE_PARAMETERS

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的庫裡。QuotationSQL>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指令不對字元集超集進行檢查： QuotationSQL>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,DTLMBQuotationImport: 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-----------