The same character set IMPDP error ORA-01401 in Oracle

The customer has a database that migrates from the HP platform to the Linux platform with the same character set. IMPDP when the error 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 (byte) Not null enable," account_id "VARCHAR2 (byte) Not null enable," Added_by "VARCHAR2" (BYTE), "Added_date"
VARCHAR2 (BYTE) DEFAULT sysdate) SEGMENT creation IMMEDIATE PCTFREE ten pctused-Initrans 1 Maxtrans 255 nocompress LOG GING 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 (byte) Not null enable," matrix_id "VARCHAR2 (byte) Not null enable," Group_na ME "VARCHAR2" (BYTE) not NULL ENABLE, "B
enchmark_id "VARCHAR2" (Byte), "Group_type" VARCHAR2 (2 byte), "STATUS" VARCHAR2 (1 byte), "Added_by" VARCHAR2 (MB), " Added_date "VARCHAR2" (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 (byte) Not null enable," Group_name "VARCHAR2 (MB) Not null enable," Create_ By "VARCHAR2" (MB), "Create_date" Varc
HAR2 (Byte) default sysdate, "Update_by" VARCHAR2 (Byte), "Update_date" VARCHAR2 (byte) default sysdate) SEGMENT CR Eation IMMEDIATE PCTFREE pctused Initrans 1
Processing Object Type Schema_export/table/table_data

Not only in the Guide table structure + data when the error, alone guide metadata, also reported the above error.

Normally, ORA-01401 is because the length of the word section is long when the insert is made. If the definition field is VARCHAR2 (10) and 11 characters are inserted, an error is made.

and IMPDP error ORA-01401 is often due to the character set problem, you can refer to ora-01401/ora-12899/ora-01461 while importing Or Loading the Data in a Al32utf8/utf8 ( Unicode) Or Other multibyte Nls_characterset Database. (Doc ID 1297961.1).

But the client has exactly the same database source and target character set. And with Csscan check, also found no error.

Why the customer's this library, not even guide the data, in the guide metadata when the error ORA-01401 it?

Further examination, and finally found the problem. The definition of this table is seen through METADATA.GET_DDL:

Dbms_metadata. GET_DDL (' TABLE ', ' ass_acchsht_green_memory ', ' myordata ')
---------------------------------------------------- ----------------------------
 
  CREATE TABLE "Myordata". Ass_acchsht_green_memory
   (    "green_id" VARCHAR2) not NULL ENABLE,
 
        "account_id" VARCHAR2 not NULL ENABLE,
         "Added_by" VARCHAR2,
        "Added_date" VARCHAR2 ( ) DEFAULT sysdate 
  ) SEGMENT creation IMMEDIATE
  PCTFREE-pctused-Initrans 1 maxtr ANS 255
nocompress LOGGING
  STORAGE (INITIAL 65536 NEXT 1048576 MIN
Extents 1 maxextents 2147483645
& nbsp Pctincrease 0 freelists 1 freelist GROUPS 1
  buffer_pool default flash_cache default
Cell_flash_cache defaul T)
  tablespace "Myordata"

Notice the 9th line here, "Added_date" VARCHAR2 DEFAULT sysdate. And in fact Sysdate is going to be 19 characters:

Sql> Select Length (sysdate), LENGTHB (sysdate) from dual;

LENGTH (sysdate) LENGTHB (sysdate)
--------------- ----------------
19 19

Sql>

So the table definition here is wrong, and since there is a default sysdate, then the field type should be a date type. If defined as VARCHAR2 (16), then the input is character type, should not take the default sysdate.