Skip to Main Content
  • Questions
  • impdp truncate date imported in timestamp

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Nicola.

Asked: September 23, 2020 - 6:19 am UTC

Last updated: September 23, 2020 - 1:52 pm UTC

Version: 19

Viewed 1000+ times

You Asked

Hi,

I created a table with a date column in an Oracle 18 database and I filled it with some date values:
create table test_date (col1 date);
insert into test_date values (sysdate);
insert into test_date values (sysdate);
select * from test_date;
COL1
-------------------
22.09.2020 16:33:05
22.09.2020 16:33:15

The same table is created in an Oracle 19 database but with a timestamp(6) column:
create table test_date (col1 timestamp(6))

If I import the table from 18 to 19 the date values are truncated (I used a database link but the error occurs also with a dumpfile):
impdp system/...@ora_instance CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE
TABLES=user_in_18db.test_date network_link=db_link_from_19_to_18
remap_schema=user_in_18db:user_in_19db
Import: Release 19.0.0.0.0 - Production on Tue Sep 22 16:45:26 2020
Version 19.5.0.0.0
...
. . "USER_IN_19DB"."TEST_DATE" 2 Zeilen importiert
Job "SYSTEM"."SYS_IMPORT_TABLE_01" erfolgreich um Di Sep 22 16:45:37 2020 elapsed 0 00:00:10
abgeschlossen

but
select * from test_date; -- in the Oracle 19 database
COL1
-------------------
22.09.2020 00:00:00
22.09.2020 00:00:00

For output I am using:
NLS_TIMESTAMP_FORMAT = 'DD.MM.YYYY HH24:MI:SS'
NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS'

YS

Nicola

and Chris said...

Yep, that's a bug (it's already logged as 12905984).

There's a problem with the date -> timestamp conversion.

The best fix is to use the date data type in the target column too, avoiding conversion problems.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.