oracle中同字符集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

不仅仅是在导表结构+数据的时候报错，单独导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"

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

SQL> select length(sysdate),lengthb(sysdate) from dual;
 
LENGTH(SYSDATE) LENGTHB(SYSDATE)
--------------- ----------------
             19               19
 
SQL>

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