While trying to import a DMP at a customer’s database, I came across the below error:
ORA-56938: no secondary time zone data file being loaded by on-demand or a datapump job
After checking the datapump jobs:
SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;
It returned -> DST_UPGRADE_STATE DATAPUMP(2)
Then I ran the below process to resolve it:
sqlplus / as sysdba
ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;
exec dbms_dst.unload_secondary;
After that, the query returned:
DST_UPGRADE_STATE DATAPUMP(1)
But the import process could not be successful since we had one datapump job still.
So the above process had to run one more time:
exec dbms_dst.unload_secondary;
In order for the query to return zero secondary datapump jobs.
SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;
–After the successful commands this will not show a secondary datapump
After resolving the above, the import managed to run succesfully.
Written by Nikos Minaidis
13/05/2021
Nikos Minaidis 