This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,906 Users
  • 2,269,775 Discussions


Relax requirement on impdp blocked by timezone mismatch

User51642 Yong Huang
User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge
edited Sep 17, 2021 10:57PM in Database Ideas - Ideas

According to

Impdp Fails With ORA-39002: Invalid Operation (Doc ID 2482971.1)

ORA-39002 occurs during a Data Pump Import due to "having a Lower version of Oracle Timezone Version in the target database and a Higher version of Oracle Timezone Version in the source database".

It's understandable that import should not proceed if timezone sensitive data will be misinterpreted if wrong timezone is applied to it. But Oracle categorically blocks import even if the data does not have any timezone-related data. My test of importing a single table with a single column of data type INT fails with this error, when the target database (18c) timezone version (31) is lower than that (32) in the source database (19c). This indiscriminate blocking of import reduces database usability unnecessarily.

The suggested fix is to upgrade the target database timezone (unless you create a temporary database with the same, lower version of timezone, import into it, upgrade the timezone, export, and import into the final target). But the upgrade requires total database shutdown, i.e. in case of RAC, not in rolling mode. This makes an apparently small issue a major operation headache.

The data pump team may adopt the strategy of the character set conversion so that a tool or impdp itself scans the dump file for timezone-sensitive data, and we're offered an option to import (with a warning) if the target timezone version is lower because it's determined the dump file has no timezone-sensitive data. If this is not done, during import, if such data is encountered, it's not too late to abort the import at this time.

User51642 Yong HuangberxPeterGMark_TplhtyukMr.Leo
6 votes

Active · Last Updated


  • Mark_T
    Mark_T Member Posts: 56 Blue Ribbon

    I agree with you. If there is no timezone related data in the dump file, there is no reason to block the import. This is rapidly becoming a major pain for us since we frequently exchange data with our customers, yet we do not have any timezone data in our schemas. Does Oracle expect everyone to always use the same timezone version? That would be kind of ridiculus.

    User51642 Yong Huang
  • User51642 Yong Huang
    User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge

    This DATABASE IDEAS forum is full of good ideas. It sounds so good that I recommended this to Red Hat customer (or technical) support, saying there should be a Linux Ideas forum on But I don't do that any more, because look, how many ideas here are taken by Oracle? The very popular "COMMENTs for indexes" idea (a few down below) initiated exactly 7 years ago is still an idea, even though implementing it would take less than 1% of the time to fix a typical CBO bug. We may as well call this forum DATABASE WISHFUL THINKING or FOREVER IDEAS.

  • Mr.Leo
    Mr.Leo Member Posts: 41 Blue Ribbon

    I totaly vote for this, although I fear it might never be implemented for two reasons.

    First one I found here:

    In this article Mike Dietrich give many details about the time zone and one specifically is this:

    "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."

    So it is kind of unpratical for impdp scan some 100GB, 200GB or even more of data just looking for time zone data and just then start the import job.

    About importing the data regardless of the different time zone version ("believing" there is no time zone in the dumpset), I asked a question at asktom about this very same situation and also commented that they could add this option, importing as is and skipping possible time zone data because of incompatibilities (the whole conversation is here, if anyone would like to check:

    Connor answer as simple, but objective:

    "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"

    Also simply aborting the import could cause more harm than good; imagine waiting 5 hours for an import to finish and hitting the problem; If warned at the start you could solve the problem before wasting 5 hours or work.

    So no, unfortunetelly I don't see any real solution for this situation in a near future.

    **** ON THE OTHER HAND ****

    An alternative way could be during export time: instead of expdp getting the TSTZ version right at the start of the export, expdp could initialize it with a default lower value (let's say 18). For each table unloaded it could check if there actually is any time zone column/data and just then it would update the real TSTZ version from the source database in the dumpset.

    If effectivelly no time zone column exists in the source database, the dump will always have a lower acceptable version to be imported by newer versions of Oracle.

    It could have some performance impacts for expdp, though: having to checking each table being unloaded might add some overhead to the export job.

  • User51642 Yong Huang
    User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge

    Thanks Mr. Leo for passing along valuable comments from well respected experts. Their comments make us think of more intricacies we didn't think of. Also I like your proposed solution. It contradicts your "I don't see any real solution for this situation".

    On the other hand, I don't see why scanning the big dump file before actually importing its data is an issue. How much more time will it add to the time to import the file without scanning? I doubt it will be more than 10%. Import will read every bit of data anyway.

    Import ignoring time zone data will corrupt the data dictionary? If that truly can happen, will excluding FULL expdp and impdp make a difference?

    I think data pump can learn from the character set conversion in the sense that a tool or impdp itself scans the data for timezone-sensitive data. If it reports none, we can safely assume import ignoring time zone data should not corrupt anything (much less data dictionary). This may be the best solution, as we spend time analyzing the data before the scheduled downtime for data migration with data pump.

    In case people forget, before the command to flush buffer cache was implemented, some people laughed at the idea saying Why do you need that. In Oracle's long history, a few other ideas were also laughed at before they were later implemented by Oracle. I hope this is not the case here.