Thursday, June 8, 2023

Oracle 19c import issues ORA-31693, ORA-02354, ORA-39002, ORA-39405

When performing import come across the following issue

ORA-31693: Table data object "ORCL"."ITEMCG" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39840: A data load operation has detected data stream format error .
ORA-39844: Bad stream format detected: [klaprs_62] [139751105749101] [139751105749012] [4] [2] [2065583] [] []

User may encounter this issue when new column added to a table with cokumn optimization enabled and the same column was modified.

Below are the workarounds

  • Use access_method=EXTERNAL_TABLE during export
  • Prior export add and drop a dummy column to the problematic table using
SQL> ALTER TABLE <table_name> ADD dummy number;
SQL> ALTER TABLE <table_name> DROP dummy number;
  • Take export of failed table and import using CONTENT=DATA_ONLY as tableau structure already imported.

The fix for this bug was initially available on 19.13 and above see if it helps.

Also, I come across below issues when importing data

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

User may encounter this issue when export from higher time zone version and importing into lower time zone version.
  • Patch the target database to higher or equal to source time zone patch or DST TZ version
  • Create a database with same time zone and perform export/import
For latest DST patches refer,
  • Oracle RDBMS and OJVM DST-related notes
  • Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

Hope this helps,

Thanks & Regards

No comments:

Post a Comment