IMPDP Job Failing with ORA-54002

Question

I am trying to import a dump file from an 11g database into a 19c database instance. I am running the job as SYSTEM as recommended by Oracle. The job starts but quickly devolves into failing SQL errors that all share the same:

ORA-54002: only pure functions can be specified in a virtual column expression. 

I have done some research on that error and have found some indications that this was an issue or bug solved after Oracle 12. The following blog addresses the issue:

Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2

...and discusses a workaround that invloves recoding the SQL.

I am creating a DDL file from the dump to give me all the code to work with.

My questions are:

• Is there an alternative to re-writing all the code in the dump file and running that against the impdp job?
• Is that even a possibility?
• Are there other options for creating a new dump file from the 11g instance that can work with the impdp into 19c?

Answer 1

I think this document sums it up nicely:

10 Behavior Changes, Deprecated and Desupported Features for Oracle Database (Oracle | Docs)

Review for information about Oracle Database 19c changes, deprecations, and desupports, as well as deprecations and desupports in Oracle Database releases 18c, 12.2, and 12.1.
...
In addition to new features, Oracle Database releases can modify, deprecate or desupport features, and introduce upgrade behavior changes for your database
...

It doesn't even contain any reference to 11.x versions.

I guess your databases contains some features that may have been desupported.

Recommended Solutions

Pre-Upgrade Information Tool

You might be able to pin-point issues within your database, with information provided in the article Using the Pre-Upgrade Information Tool for Oracle Database. However, based on the information provided in your question, I guess this could take a while.

The Pre-Upgrade Information Tool is also mentioned in Oracle's whitepaper: Upgrade and Migrate to Oracle Database 19c

How to choose and use the best available method to upgrade or migrate your current Oracle database to Oracle Database 19c.

Consultant

If you are in the process of upgrading a database, then you might want to consider reaching out to a consultant who can possibly provide you with a clear concise list of features/functions/... which are no longer supported in the different database versions.

Answering Your Questions

Is there an alternative to re-writing all the code in the dump file and running that against the impdp job?

See above suggestions.

Is that even a possibility?

Maybe not, depending on the features that were used in your 11g database. It might work and it might not work.

Are there other options for creating a new dump file from the 11g instance that can work with the impdp into 19c?

You could create the DDL of all objects from the old database and try to create them in a new 19c database. But even then, you might be creating objects that contain desupported features.

Good luck.