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。