Oralce导入数据时提示ORA-12899错误value too large for column
来源:互联网 发布:sql培训班昆山 编辑:程序博客网 时间:2024/06/05 06:03
Oralce导入数据时提示ORA-12899错误value too large for column
www.111cn.net 更新:2013-11-21 编辑:future 来源:转载
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 * 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 * 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 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
重新启动数据库,再次导入,即可成功.
代码如下 复制代码
[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)。
上一篇123 下一篇
上一页:Oracle只读事务测试学习笔记 下一页:oracle数据库备份图文详解
相关内容
2016-04-27oralce提示ORA-30025: DROP segment ‘_SYSSMU559$’ (in undo tablespace) not allowed错误
from:http://www.111cn.net/database/Oracle/55037.htm
- Oralce导入数据时提示ORA-12899错误value too large for column
- Oralce导入数据时提示ORA-12899错误value too large for column
- ORA-12899: value too large for column
- ORA-12899: value too large for column
- ORA-12899 – Value too large for column string
- goldengate ORA-12899: value too large for column
- ORA-12899: value too large for...
- ORA-12899: value too large for column "SOAU"."SJQY_QTSBSPEC"."PROPERTY_6" (actual: 566, maximum: 500
- OGG: NLS_LENGTH_SEMANTICS报错信息ora-12899 value too large for column
- Oracle导数时报错:ORA-12899: value too large for column
- 在windows 下用tools导出导入Repository出错 value too large for column "SIEBEL"."S_ACCELERATOR
- gcc 编译错误 Value too large for defined data type
- Caused by: com.sap.db.jdbc.exceptions.BatchUpdateExceptionSapDB:inserted value too large for column
- mysql5中大数据错误:Packet for query is too large (***>***). You can change this value on the ser
- Mysql 导入psc 文件时,数据列过多,提示Row size too large(>8126)
- mysql导入备份文件(.sql)报错:Data too large for column
- mysql保存数据提示:Out of range value for column错误
- mysql保存数据提示:Out of range value for column错误
- 第十七周-OJ-A指针输出最大值
- 大数据处理分析工具
- win10下C++ IDE 异常崩溃
- 2460: [BeiJing2011]元素
- 国产AR SDK介绍+国外arSdk带过
- Oralce导入数据时提示ORA-12899错误value too large for column
- Java事务处理总结
- 第16周OJ-3
- 【目标检测】Fast RCNN算法详解
- javascript判断变量是否为空的方法
- 笔记:常用的几个小片段
- Json方式获取网络数据
- win10内部错误2502,2503最有效的解决方案!
- Spring MVC 入门指南(一):HelloWorld