Friday, September 17, 2010

ORA-31694: master table "SYS"."SYS_IMPORT_FULL_01" failed to load/unload

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It's amazing how much time one can waste because of certain bugs.
Yesterday evening i got the following stack of errors upon importing (IMPDP) a dump made on Oracle 11.1.0.7.0 to 11.1.0.6.0.
ORA-39002: invalid operation
ORA-31694: master table "SYS"."SYS_IMPORT_FULL_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "SYS"."SYS_IMPORT_FULL_01".
ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier
My first reaction was to tamper with the IMPDP parameters, on the false assumption that i was doing something wrong (who knows why i always assume I'm wrong...).
After various attempts, i started looking around and found several references that unfortunately were misleading. Someone claimed a VERSION=11.1.0.6.0 parameter was missing, another suggested a wrong character set or a corrupt dump and so on. Every round implied a new dump to be made available by the customer, downloaded and imported.
Finally, this morning, i found a rather recent OTN thread were it was made clear that the above errors are a consequence of bug 7590679 in 11.1.0.7.

The quick solution is to perform the export (EXPDP) adding parameter VERSION=10.2 (with some caveats as per cited metalink note) or alternatively install the patch for upgrading from 11.1.0.6 to 11.1.0.7.

As an experiment i also tried importing the downgraded dump to XE and it worked like a charm.

See message translations for ORA-31694 and search additional resources.

3 comments:

Anonymous said...

Thanks for this post, just ran into same problem + solution.

Anonymous said...

Grazie mille!! Finalmente siamo potuto! from Japan

Steef D. said...

Thanks, exactly the error I was facing.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles