使用sys用户imp导入数据遇到字符集转换后列长度不够报错

使用sys用户imp数据遇到字符集转换时列长度不够的报错，ORA-12899: value too large for column "SYS"."M_ASH"."ACTION" (actual: 40, maximum: 32)。从报错可以看到具体的表、字段信息，报错SYS用户的M_ASH表的ACTION列，实际需要40byte,这里最大是32byte.原因是原来导出、导入过程的字符集转换。手动对表的列进行字段长度修改，重新使用DATA_ONLY=y参数导入即可。顺便记录一下使用sys用户imp/exp时需要转义，格式参考如下: imp \'sys/ as sysdba\' full=y DATA_ONLY=y file=m_ash_531.dmp imp \'sys/oracle as sysdba\' full=y DATA_ONLY=y file=m_ash_531.dmp

<span style="color:#000000">[<a data-cke-saved-href="/cdn-cgi/l/email-protection" href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a> ~]$ imp \'sys/<a data-cke-saved-href="/cdn-cgi/l/email-protection" href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a> as sysdba\' full=y file=m_ash_531.dmp
Import: Release 11.2.0.4.0 - Production on Mon Jun 5 10:34:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SYSTEM, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
. importing SYSTEM's objects into SYS
. importing SYS's objects into SYS
. . importing table "M_ASH"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SYS"."M_ASH"."ACTION" (actual: 40, maximum: 32)
Column 1 31633
Column 2 2183090312
………………
Column 47 23470 rows imported
Import terminated successfully with warnings.</span>

修改字段长度

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 10:35:43 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> truncate table M_ASH;

Table truncated.

SQL> desc M_ASH;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NUMBER
DBID NUMBER
………………
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)
FLAGS NUMBER

SQL> alter table M_ASH modify ACTION VARCHAR2(42);

Table altered.

重新导入即正常：

]$ imp \'sys as sysdba\' full=y DATA_ONLY=y file=m_ash_531.dmp
Import: Release 11.2.0.4.0 - Production on Mon Jun 5 10:39:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SYSTEM, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
. importing SYSTEM's objects into SYS
. . importing table "M_ASH" 23471 rows imported
Import terminated successfully without warnings.

SQL> select count(*) from M_ASH;
COUNT(*)
----------
23471