Oralce导入数据时提示ORA-12899错误value too large for column

更新:2013-11-21 编辑:future 来源:转载

今天在把以前备份的数据导入到oracle时，发现提示 ORA-12899错误value too large for column了，这个就是数据太大的问题，下面我来把解决过程与各位同学分享一下。

从生产导出数据到测试,出现如下错误

ORA-02374: conversion error loading table "SWTONLINE"."TBL_TRANS_LOG_HISTORY_B"
ORA-12899: value too large for column MER_ADDR_NAME (actual: 54, maximum: 40)

初步估计为字符集差异导致.中文在UTF-8里占3个字节，ZHS16GBK里占2个字节

查看测试库字符集

代码如下

复制代码

sys@PROD> * from V$NLS_PARAMETERS   2  ; PARAMETER                                                        VALUE ---------------------------------------------------------------- ---------------------------------------- NLS_LANGUAGE                                                     SIMPLIFIED CHINESE NLS_TERRITORY                                                    CHINA NLS_CURRENCY                                                     ￥ NLS_ISO_CURRENCY                                                 CHINA NLS_NUMERIC_CHARACTERS                                           ., NLS_CALENDAR                                                     GREGORIAN NLS_DATE_FORMAT                                                  DD-MON-RR NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE NLS_CHARACTERSET                                                 AL32UTF8 NLS_SORT                                                         BINARY NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY                                                ￥ NLS_NCHAR_CHARACTERSET                                           AL16UTF16 NLS_COMP                                                         BINARY NLS_LENGTH_SEMANTICS                                             BYTE NLS_NCHAR_CONV_EXCP                                              FALSE 19 rows selected. Elaed: 00:00:00.00

查看生产库字符集

代码如下

复制代码

sys@ORADB>select * from V$NLS_PARAMETERS; PARAMETER                                                        VALUE ---------------------------------------------------------------- ---------------------------------------- NLS_LANGUAGE                                                     AMERICAN NLS_TERRITORY                                                    AMERICA NLS_CURRENCY                                                     $ NLS_ISO_CURRENCY                                                 AMERICA NLS_NUMERIC_CHARACTERS                                           ., NLS_CALENDAR                                                     GREGORIAN NLS_DATE_FORMAT                                                  DD-MON-RR NLS_DATE_LANGUAGE                                                AMERICAN NLS_CHARACTERSET                                                 ZHS16GBK NLS_SORT                                                         BINARY NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY                                                $ NLS_NCHAR_CHARACTERSET                                           AL16UTF16 NLS_COMP                                                         BINARY NLS_LENGTH_SEMANTICS                                             BYTE NLS_NCHAR_CONV_EXCP                                              FALSE 19 rows selected. Elapsed: 00:00:00.00

更改测试库的字符集和生产库一样

代码如下

复制代码

sys@PROD>SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. sys@PROD>STARTUP MOUNT ORACLE instance started. Total System Global Area  939495424 bytes Fixed Size                  2233960 bytes Variable Size             671091096 bytes Database Buffers          260046848 bytes Redo Buffers                6123520 bytes Database mounted. sys@PROD>ALTER SYSTEM ENABLE RESTRICTED SESSION; System altered. Elapsed: 00:00:02.19 sys@PROD>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; System altered. Elapsed: 00:00:00.02 sys@PROD>ALTER SYSTEM SET AQ_TM_PROCESSES=0; System altered. Elapsed: 00:00:00.01 sys@PROD>ALTER DATABASE OPEN; Database altered. Elapsed: 00:00:05.09 sys@PROD>ALTER DATABASE ?CHARACTER SET ZHS16GBK ; ALTER DATABASE ?CHARACTER SET ZHS16GBK                * ERROR at line 1: ORA-00911: 无效字符 Elapsed: 00:00:00.00

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

代码如下	复制代码
sys@PROD>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; Database altered. Elapsed: 00:00:53.35 SQL>SHUTDOWN IMMEDIATE SQL>STARTUP

重新启动,再次导入,即可成功.

代码如下	复制代码
[oracle@idata ~]$  impdp  '/ as sysdba ' directory=backup dumpfile=cil_20130702.dmp logfile=cil_20130703.log table_exists_action=replace schemas=EPAYMENT,LOGSDB,PREPAID,RISK,SWTSETTLE,SWTONLINE

补充一下oracle字符编码问题

通常会查询NLS_CHARACTERSET（字符集），NLS_NCHAR_CHARACTERSET（国家字符集），应该要存储多种语言，需要字符集为UTF-8。

代码如下	复制代码
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

 
结果UTF-8，OK。
 
查看没问题的DB里 FieldA varchar2(10 char)
 
查看有问题的DB里 FieldA varchar2(10)
 
（没出现问题之前还真没注意到这两种定义是有区别的。。。）
 
UTF-8里一个中文字符是3 bytes，从上面的定义可以看出来，如果char/byte 定义导致的可存储数据长度相差很大了。
 
设置参数NLS_LENGTH_SEMANTICS可以在create table时对CHAR 或者VARCHAR2列指定使用字节（byte）或者字符（character）来定义长度。
 
NCHAR, NVARCHAR2, CLOB, and NCLOB 列都是基于字符（character）的。
 
NLS_LENGTH_SEMANTICS不会影响到SYS和SYSTEM用户表，数据字典定义都使用字节（byte）。
 
可以在定义列时候显示指定使用字节（byte）或者字符（character）来定义长度：
 
CHAR(10 BYTE)  - 无论NLS_LENGTH_SEMANTICS设置成什么，都使用字节（byte）。
 
CHAR(10 CHAR) - 无论NLS_LENGTH_SEMANTICS设置成什么，都使用字符（char）。