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."
Source:
https://mikedietrichde.com/2019/05/14/data-pump-the-time-zone-pitfalls/ 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.
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 :-)