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

爱被打了一巴掌 2022-07-13 03:16 441阅读 0赞

[www.111cn.net][] 更新:2013-11-21 编辑:future 来源:转载

[http://www.111cn.net/database/Oracle/55037.htm][http_www.111cn.net_database_Oracle_55037.htm]

今天在把以前备份的数据导入到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>select][sys_PROD_select] \* 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.

Elapsed: 00:00:00.00

查看生产库字符集

代码如下 复制代码

[sys@ORADB>select][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               ([www.111cn.net][])                                          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][sys_PROD_SHUTDOWN] IMMEDIATE;  
 Database closed.  
 Database dismounted.  
 ORACLE instance shut down.  
 [sys@PROD>STARTUP][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][sys_PROD_ALTER] SYSTEM ENABLE RESTRICTED SESSION;

System altered.

Elapsed: 00:00:02.19  
 [sys@PROD>ALTER][sys_PROD_ALTER] SYSTEM SET JOB\_QUEUE\_PROCESSES=0;

System altered.

Elapsed: 00:00:00.02  
 [sys@PROD>ALTER][sys_PROD_ALTER] SYSTEM SET AQ\_TM\_PROCESSES=0;

System altered.

Elapsed: 00:00:00.01  
 [sys@PROD>ALTER][sys_PROD_ALTER] DATABASE OPEN;

Database altered.

Elapsed: 00:00:05.09  
 [sys@PROD>ALTER][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][sys_PROD_ALTER] DATABASE CHARACTER SET INTERNAL\_USE ZHS16GBK;

Database altered.

Elapsed: 00:00:53.35

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

代码如下 复制代码

\[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）。

上一页：Oracle只读事务测试学习笔记      下一页：oracle数据库备份图文详解

相关内容  
2016-04-27oralce提示ORA-30025: DROP segment ‘\_SYSSMU559$’ (in undo tablespace) not allowed错误  
from:http://www.111cn.net/database/Oracle/55037.htm

[www.111cn.net]: http://www.111cn.net
[http_www.111cn.net_database_Oracle_55037.htm]: http://www.111cn.net/database/Oracle/55037.htm
[sys_PROD_select]: mailto:sys@PROD>select
[sys_ORADB_select]: mailto:sys@ORADB>select
[sys_PROD_SHUTDOWN]: mailto:sys@PROD>SHUTDOWN
[sys_PROD_STARTUP]: mailto:sys@PROD>STARTUP
[sys_PROD_ALTER]: mailto:sys@PROD>ALTER