My Oracle Support Banner

IMPDP - ORA-39083 and ORA-01847 (Day Of Month Must Be...) On Import Of Table (Doc ID 1632220.1)

Last updated on MAY 18, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Oracle Database - Personal Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Oracle Database - Standard Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Enterprise Manager for Oracle Database - Version 10.1.0.2 to 12.1.0.5.0 [Release 10.1 to 12.1]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data .Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

When attempting to import a table with a default date format, the following error occurs.

$ impdp system/<password> DIRECTORY=my_dir DUMPFILEe=expdp_tc.dmp LOGFILE=impdp_tc.log TABLES=tc.my_tab

Import: Release 11.2.0.4.0 - Production on Wed Mar 5 10:38:53 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** DIRECTORY=my_dir DUMPFILEe=expdp_tc.dmp LOGFILE=impdp_tc.log TABLES=tc.my_tab
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TC"."MY_TAB" failed to create with error:
ORA-01847: day of month must be between 1 and last day of month
Failing sql is:
CREATE TABLE "TC"."MY_TAB" ("NR" NUMBER, "TXT" VARCHAR2(10 BYTE), "COL3" DATE DEFAULT '9999-01-01 00:00:00') SEGMENT CREATION IMMEDIATE PCTFREE 10 ...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Mar 5 10:38:57 2014 elapsed 0 00:00:03

The table was created with following DDL:

 

Changes

 NONE

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.