How To Avoid ORA-39405 During a Data Pump Import

Data Pump 2 Minutes

Why did my Data Pump import fail?

Import: Release 19.0.0.0.0 - Production on Mon Jun 29 06:17:06 2025
Version 19.31.0.0.0

ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 44 into a target database with TSTZ version 45.

It all comes down to the TIMESTAMP WITH TIME ZONE (TSTZ) data type.

The database uses the timezone file to translate your TSTZ data to the right time. If the Daylight Saving Time (DST) rules change, a newer timezone file ensures that you still get the right data.

Data Pump refuses to import the data because differing timezone file versions could lead to incorrect TSTZ values.

The Solution

Simple: apply at least Release Update 19.27 and install the Data Pump bundle patch.

Data Pump now converts your TSTZ values when needed. During data, loading Data Pump uses an internal function called ORA_DST_CONVERT that does all the magic. The function can convert between both older and newer timezone file versions. Because each row requires a function call during import, you should expect a small performance overhead. I expect it to be negligible.

Remember, you can install the Data Pump bundle patch online without any downtime.

But I Don’t Have Timezone Data?

If your dump file doesn’t contain any TSTZ data, it’s safe to import even if the timezone file versions differ.

Older versions of Data Pump were overly cautious. Even without TSTZ data, they immediately failed when they detected a timezone file mismatch.

Why Does This Happen?

A few years ago, Oracle started shipping newer timezone file versions with every Release Update. It’s convenient and ensures that you always have the latest files installed.

But when you create a database, it gets the latest timezone file automatically. As you create databases over time, they’ll end up using different timezone file versions.

This is when you start seeing ORA-39405 in Data Pump.

In the good, ol’ days, timezone files were separate patches. Most people didn’t install them, so all of their databases ended up using the same timezone file version.

That’s It

If you have different timezone file versions in your source and target databases, Data Pump must convert your TSTZ columns to match the target database’s timezone file version. This requires at least Release Update 19.27 and the Data Pump bundle patch.

Happy importing!

Published

2 thoughts on “How To Avoid ORA-39405 During a Data Pump Import”

  1. In ExaCS/ExaXS, it’s safe to manually apply DPBP or should i check with oracle support? i ask this because the OCI console does not provide a tool to apply MRP, DPBP, CSPU etc.

    Like

    Reply

    1. Hi,

      You can add other patches to your Oracle home on Exadata systems as well. What I recommend is that you build your own “Software image” and add all the patches. Then use that “Software image” instead of using the default option when creating the new Oracle home.

      One thing to note about the Data Pump bundle patch is that it’s not RAC rolling in 19c (and also 26ai – but we might lift that restriction later on). You probably have RAC databases on your Exadatas, so this might be a problem to you.

      But you can apply the Data Pump bundle patch on a running database – even a running RAC database. I explain that here:

      How To Install Data Pump Bundle Patch On Running Database Without Downtime

      In OCI, you’d need to connect to the nodes and perform the patching manually. But that’s still fully supported.

      Hope that helps,
      Daniel

      Like

      Reply

Leave a comment