another ouch: test-case
do a datapump export on 11.2.0.4 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 ); begin for i in 1..10000 loop 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; commit; end; / select count(*) from test; SQL> select min(b),max(b) from test; MIN(B) --------------------------------------------------------------------------- MAX(B) --------------------------------------------------------------------------- 06-JUN-14 09.07.46.214879 PM +02:00 06-JUN-14 09.07.46.214879 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 12.1.0.1 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; A B ---------- --------------------------------------------------------------------------- 636 06-JUN-14 09.07.46.000000 PM +02:00 637 06-JUN-14 09.07.46.000000 PM +02:00 638 06-JUN-14 09.07.46.000000 PM +02:00 639 06-JUN-14 09.07.46.000000 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