Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Leandro.

Asked: April 08, 2022 - 6:53 pm UTC

Last updated: June 05, 2024 - 2:37 pm UTC

Version: Oracle 19

Viewed 10K+ times! This question is

You Asked

Hi there, masters.

I'm facing a rather weird situation regading the TSTZ configuration. A full detailed description of my initial problem is here in this post I made some months ago:

tl;dr: Oracle 19 does not import data sets if the source has a bigger TSTZ version. This never happened with 12c; in fact we ignored completely the TSTZ version aspect since we don't have any TSTZ data in our data models.

Accordingly to all docs and blog entries I could find on line, the solution is to upgrade the version to at least match the source server's one. But here is ou main problem: we cannot really do it.

Giving we exchange data (both send and recive) from several clients, they can perfectly be on different versions and we will end up with following scenario:

Client X TSTZ is 36, our is 32 = error. We upgrade ours to 36 and then we can import his data and he can also import ours (both versions match).

Client Y TSTZ is 34, our now is 36. We can import his data (our is bigger) but he cannot import ours (since his is lower).

We cannot impose to our clients to be at a X specific version, this is not realist. Some might want/need to be on the latest release while others might not/could not do it.

Was this restriction thought through when imposed in 18c? I cannot believe such scenario was not foreseen. There must be a really silly detail that I'm overlooking on all of this.

So, what are your insights on this situation, masters?

and Connor said...

Thanks for your patience. I asked the DataPump team and got some (sad) news

"In version 21c and later, there are new implementations of TSTZ/timezone support. This new support was backported to *some* 19c variants but not all. Certain things haven’t changed. When you do an export, the timezone info from the database is recorded in the dump file. When you import that dump file, the import database has to be at that version or a newer version. It’s OK to import a dump file with an older timezone version as long as that timezone data file is available. In 12c, there was a lot less checking of timezone versions. Data Pump would note the potential incompatibility and then plough full speed ahead, occasionally resulting in ORA-600’s and crashes…and sometimes it would just work. The additional checking was added to avoid some of the catastrophic failures."

Bottom line - you need to people to align their timezones to avoid data corruptions.

I've logged an enhancement where DataPump should ideally at export time, check if the contents refer to a timezoned column of any sort to ease up this restriction, but that would be a fair way off.


  (4 ratings)

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


Leandro, April 20, 2022 - 1:41 pm UTC

Thanks for your answer, Connor.

"you need to people to align their timezones to avoid data corruptions"

Like I said, this requirement would be impratical for us since we have dozens of clients on Oracle and we can't ask them all to be up to date with the latest version, nor skipping patches or necessary updates to keep the same version as us. Our idea for the moment is using a "translation" Oracle 12 server since this version does not check the TSTZ when importing and generates a lower one when exporting. But this additional step is kind of burdensome :

Client sends his Ora19 dump -> We import it into an Ora12 -> We copy the data into our Ora19

We copy our Ora19 data into the Ora12 server -> we export the Ora12 data -> the client imports the 12c dump into his Ora19

We found a really interesting post by Mike Dietrich that talks about this same situation (I wrote him in the comment section but he did not answer me). In his blog he explains that:

"My colleague exclaimed: “But I don’t have any time zone data in that file!”. And he may be right. But Data Pump does not know. And it would need now to scan an entire dump file in order to check if there’s any offending time zone data in the dump. This may take a long time. And hence, it has been implemented to deny such attempts."


I thank you for taking time to analyze my situation and your effort of asking for an enhancement for DataPump, but I would like to suggest a different solution: adding an option into impdp to ignore the TSTZ check. We already have the VERSION option in expdp where we can define the server compatibility level, so adding a way to import regardless of the timezone settings would solve this situation. Given it would be a very specific option, I would be up to the user to deal with any corruptions or errors during the import.

What do you think? Could such option be considered and, maybe, added to impdp?

Once again, thank you very much for your help.
Connor McDonald
April 27, 2022 - 2:32 am UTC

I think ignoring the timezone check is fraught with danger. Don't forget that dictionary tables also have timezones in some places, which means it would akin to say: "I don't mind if the dictionary gets corrupted".

That sounds like a nightmare for Support :-)


Carl B., April 27, 2022 - 7:14 pm UTC

The official documentation of Oracle is in contradiction with what you are saying Connor. See here:

Where we can find:


Export and import jobs that have TIMESTAMP WITH TIME ZONE data are restricted.

Understanding TIMESTAMP WITH TIME ZONE Restrictions
Carrying out export and import jobs that have TIMESTAMP WITH TIME ZONE data requires understanding information about your time zone file data and Oracle Database release."

If Oracle don't modify the behavior of these tools, the documentation should be reviewed.

Best regards.

Connor McDonald
April 28, 2022 - 3:33 am UTC

That is correct - the docs should be more precise. I'll log a bug

Thanks for letting us know.

TSTZ issue with Oracle database 19c versions ...

Peter, May 20, 2024 - 6:47 pm UTC

What issues would occur, specifically with data integrity, if one uses database links to move/copy the data from a 19c database with a later TSTZ version to another database with a older version?

DataPump error:
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 42 into a target database with TSTZ version 32.

Also, would potentially errors occur during the copy process through the database links.

We're aware that Database links tend to be slow.


Connor McDonald
May 21, 2024 - 6:57 am UTC

Mike has an excellent post on this

which talks about both supported and *unsupported* workarounds

More to Explore


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