impdp got 'ORA-01843: not a valid month'

When running impdp to load data, got errors:

ORA-39083: Object type TABLE:"DEVUSER"."TAB01" failed to create with error:
ORA-01843: not a valid month
Failing sql is:
CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000')

Try to run the script manually in SQLPLUS:

devuser@ORADB> CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000');
CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000')
                                                                                                  *
ERROR at line 1:
ORA-01843: not a valid month

Got same error. Obviously, the CREATE TABLE is using string constant as default value for data type TIMESTAMP. We know it will work only if the session TIMESTAMP format is same as the given string. Let's see what is the session TIMESTAMP format:

devuser@ORADB> variable partype number;
devuser@ORADB> variable intval number;
devuser@ORADB> variable strval varchar2(255);
devuser@ORADB> exec :partype:=dbms_utility.get_parameter_value('NLS_TIMESTAMP_FORMAT',:intval,:strval);

PL/SQL procedure successfully completed.

devuser@ORADB> col STRVAL for a50
devuser@ORADB> print strval

STRVAL
--------------------------------------------------
DD-MON-RR HH.MI.SSXFF AM

The value of session parameter NLS_TIMESTAMP_FORMAT is  'DD-MON-RR HH.MI.SSXFF AM', which is different from the string constant. Change the session parameter as following:

devuser@ORADB> alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';

Session altered.

devuser@ORADB> exec :partype:=dbms_utility.get_parameter_value('NLS_TIMESTAMP_FORMAT',:intval,:strval);

PL/SQL procedure successfully completed.

devuser@ORADB> print strval

STRVAL
--------------------------------------------------
YYYY-MM-DD HH24:MI:SS.FF6

Retry the CREATE TABLE statement:

devuser@ORADB> CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000');

Table created.

The issue is fixed. Therefore, the CREATE TABLE can be run properly by setting session parameter NLS_TIMESTAMP_FORMAT. The question is how the impdp utility sets this parameter in its own session? LOGON trigger would be a tentative answer:

devuser@ORADB> create or replace trigger temp after logon on database
  2  begin
  3   execute immediate 'alter session set nls_timestamp_format=''YYYY-MM-DD HH24:MI:SS.FF6''';
  4  end;
  5  /

Trigger created.

Try impdp again, it works. However, it may not be a best solution. If possible I would like modify the CREATE TABLE (in source database) as following before runnig expdp to create dumpfile:

CREATE TABLE "DEVUSER"."TAB01"
   ("ID" NUMBER NOT NULL ENABLE,
    "CREATED_TIME" TIMESTAMP (6) DEFAULT TO_TIMESTAMP('1970-01-01 00:00:00.000000','YYYY-MM-DD HH24:MI:SS.FF6'));

It is independent on the session timestamp format.