ouch: lost Milliseconds when expdp/impdp from to

July 22, 2014 — Leave a comment

another ouch: test-case

do a datapump export on Linux x64:

connect / as sysdba
drop user u15 cascade;
create user u15 identified by u15 default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create table to u15;
connect u15/u15
create table test ( a number, b timestamp with time zone );
for i in 1..10000
insert into test values ( i
, to_timestamp_tz ('2014-06-06 21:07:46.214879 +2:00','YYYY-MM-DD HH24:MI:SSXFF TZH:TZM' )
end loop;
select count(*) from test;
SQL> select min(b),max(b) from test;

06-JUN-14 PM +02:00
06-JUN-14 PM +02:00

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!expdp userid="'/ as sysdba'" parallel=4 dumpfile=test_%U.dmp logfile=test.log schemas=u15 directory=dir_tmp;

import the dump into a database on linux x64:

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!impdp parallel=2 userid="'/ as sysdba'" schemas=u15 dumpfile=test_%U.dmp logfile=test.log directory=dir_tmp;

… and check the timestamps:

U15@dbs101> select * from test order by 2;

---------- ---------------------------------------------------------------------------
636 06-JUN-14 PM +02:00
637 06-JUN-14 PM +02:00
638 06-JUN-14 PM +02:00
639 06-JUN-14 PM +02:00

… outch

UPDATE: One workaround is to use tradtional exp/imp
26-AUG-2014 UPDATE 2: the workaround is no workaround if you use encrypted tablespaces as exp/imp can not handle this


No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s