I'm trying to import a schema from a dump file created in another schema (same database). The liveSql script has the objects for which I'm interested, and in the case of this test, that's all that exists besides the standard Oracle stuff. The schema named "uname2" was created with the script for the schema "uname," and has had no additions.
I executed the following command line operations.
>expdp userid=uname/uname
>impdp userid=uname/uname dumpfile=uname REMAP_SCHEMA=uname:uname2
emppdp ran fine. impdp had the following errors:
ORA-39083: Object type TABLE:"UNAME2"."TEST_TABLE" failed to create with error:
ORA-00904: "UNAME"."CHECK_IS_ACTIVE": invalid identifier
Failing sql is:
ALTER TABLE "UNAME2"."TEST_TABLE" MODIFY ("IS_ACTIVE" NUMBER(*,0) GENERATED ALWAYS AS ("UNAME"."CHECK_IS_ACTIVE"("ACTIVE_FROM","ACTIVE_TO")) VIRTUAL )
It's not clear if the problem is because the tables are imported before the functions are, or if it has to do with the (apparent) mismatch of user/schema name (uname and uname2).
Yup...known limitation with DataPump. Check mos note 1542196.1 for more details, but in a nutshell ( from that note):
Until Enhancement Request 11737394 has been implemented, there are next two workarounds for the issue
Grant execute permission on the related function in the source schema to the target user
- OR -
Edit the target table's definition manually to make it point to the function residing in its own schema.