Skip to Main Content
  • Questions

  • impdp truncate date imported in timestamp

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

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