oracle 01401,同字符集impdp报错ORA-01401

客户有个数据库从HP平台迁移到linux平台，字符集相同。impdp的时候报错ORA-01401了。

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100 BYTE), "ADDED_DATE"

VARCHAR2(16 BYTE) DEFAULT sysdate) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 M

ORA-39083: Object type TABLE:"MYORDATA"."ASS_ACCHSHT_GREEN" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "MATRIX_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "B

ENCHMARK_ID" VARCHAR2(16 BYTE), "GROUP_TYPE" VARCHAR2(2 BYTE), "STATUS" VARCHAR2(1 BYTE), "ADDED_BY" VARCHAR2(100 BYTE), "ADDED_DATE" VARCHAR2(16 BYTE) DEFAULT sysdate, "U

ORA-39083: Object type TABLE:"MYORDATA"."ASS_QUITTK_GREEN" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_QUITTK_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "CREATE_BY" VARCHAR2(100 BYTE), "CREATE_DATE" VARC

HAR2(16 BYTE) DEFAULT sysdate, "UPDATE_BY" VARCHAR2(100 BYTE), "UPDATE_DATE" VARCHAR2(16 BYTE) DEFAULT sysdate) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

ProcessingobjecttypeSCHEMA_EXPORT/SYNONYM/SYNONYM

ProcessingobjecttypeSCHEMA_EXPORT/SEQUENCE/SEQUENCE

ProcessingobjecttypeSCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE

ORA-39083:ObjecttypeTABLE:"MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY"failedtocreatewitherror:

ORA-01401:insertedvaluetoolargeforcolumn

Failingsqlis:

CREATETABLE"MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY"("GROUP_ID"VARCHAR2(16BYTE)NOTNULLENABLE,"ACCOUNT_ID"VARCHAR2(16BYTE)NOTNULLENABLE,"ADDED_BY"VARCHAR2(100BYTE),"ADDED_DATE"

VARCHAR2(16BYTE)DEFAULTsysdate)SEGMENTCREATIONIMMEDIATEPCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL65536NEXT1048576M

ORA-39083:ObjecttypeTABLE:"MYORDATA"."ASS_ACCHSHT_GREEN"failedtocreatewitherror:

ORA-01401:insertedvaluetoolargeforcolumn

Failingsqlis:

CREATETABLE"MYORDATA"."ASS_ACCHSHT_GREEN"("GROUP_ID"VARCHAR2(16BYTE)NOTNULLENABLE,"MATRIX_ID"VARCHAR2(16BYTE)NOTNULLENABLE,"GROUP_NAME"VARCHAR2(100BYTE)NOTNULLENABLE,"B

ENCHMARK_ID"VARCHAR2(16BYTE),"GROUP_TYPE"VARCHAR2(2BYTE),"STATUS"VARCHAR2(1BYTE),"ADDED_BY"VARCHAR2(100BYTE),"ADDED_DATE"VARCHAR2(16BYTE)DEFAULTsysdate,"U

ORA-39083: Object type TABLE:"MYORDATA"."ASS_QUITTK_GREEN" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_QUITTK_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "CREATE_BY" VARCHAR2(100 BYTE), "CREATE_DATE" VARC

HAR2(16 BYTE) DEFAULT sysdate, "UPDATE_BY" VARCHAR2(100 BYTE), "UPDATE_DATE"VARCHAR2(16BYTE)DEFAULTsysdate)SEGMENTCREATIONIMMEDIATEPCTFREE10PCTUSED40INITRANS1

ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE_DATA

不仅仅是在导表结构+数据的时候报错，单独导metadata的时候，也报上面的错。

正常情况下，ORA-01401是因为做insert时，字段长度过长。如定义字段是varchar2(10)，插入了11个字符，从而报错。

而impdp报错ORA-01401往往是因为字符集的问题，可以参考ORA-01401 / ORA-12899 / ORA-01461 While Importing Or Loading Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database. (Doc ID 1297961.1)。

但是客户的这个数据库源和目标字符集都完全一致。且用csscan检查，也没发现报错。

为啥客户的这个库，甚至还没导数据，在导metadata的时候就报错ORA-01401了呢？

进一步检查，终于发现了问题所在。这个表的定义，通过metadata.get_ddl看到：

DBMS_METADATA.GET_DDL('TABLE','ASS_ACCHSHT_GREEN_MEMORY','MYORDATA')

--------------------------------------------------------------------------------

CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY"

( "GREEN_ID" VARCHAR2(16) NOT NULL ENABLE,

"ACCOUNT_ID" VARCHAR2(16) NOT NULL ENABLE,

"ADDED_BY" VARCHAR2(100),

"ADDED_DATE" VARCHAR2(16) DEFAULT sysdate

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MIN

EXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT)

TABLESPACE "MYORDATA"

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

DBMS_METADATA.GET_DDL('TABLE','ASS_ACCHSHT_GREEN_MEMORY','MYORDATA')

--------------------------------------------------------------------------------

CREATETABLE"MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY"

("GREEN_ID"VARCHAR2(16)NOTNULLENABLE,

"ACCOUNT_ID"VARCHAR2(16)NOTNULLENABLE,

"ADDED_BY"VARCHAR2(100),

"ADDED_DATE"VARCHAR2(16)DEFAULTsysdate

)SEGMENTCREATIONIMMEDIATE

PCTFREE10PCTUSED40INITRANS1MAXTRANS255

NOCOMPRESSLOGGING

STORAGE(INITIAL65536NEXT1048576MIN

EXTENTS1MAXEXTENTS2147483645

PCTINCREASE0FREELISTS1FREELISTGROUPS1

BUFFER_POOLDEFAULTFLASH_CACHEDEFAULT

CELL_FLASH_CACHEDEFAULT)

TABLESPACE"MYORDATA"

注意这里第9行， “ADDED_DATE” VARCHAR2(16) DEFAULT sysdate。而其实sysdate是要19个字符的：

SQL> select length(sysdate),lengthb(sysdate) from dual;

LENGTH(SYSDATE) LENGTHB(SYSDATE)

--------------- ----------------

19 19

SQL>

1

2

3

4

5

6

7

SQL>selectlength(sysdate),lengthb(sysdate)fromdual;

LENGTH(SYSDATE)LENGTHB(SYSDATE)

-------------------------------

1919

SQL>

所以这里的表定义是错误的，既然有default sysdate，那么字段类型应该是date型。如果定义成varchar2(16)，那么录入的是字符型，不应该带上default sysdate。