1

I'm trying to impdp a schema with just one table on it. The impdp parfile looks like this:

LOGFILE=scott_impdp.log
DUMPFILE=scott.dmp
SCHEMAS=scott
REMAP_SCHEMA=scott:scott2
REMAP_TABLESPACE=scott:scott2
EXCLUDE=grant
PARALLEL=8
CLUSTER=N
TRANSFORM=disable_archive_logging:Y

However, I'm getting these errors:

ORA-39002: invalid operation
ORA-39168: Object path GRANT was not found.

If I'm not mistaken, EXCLUDE=grant is valid syntax according to https://docs.oracle.com/database/121/SUTIL/GUID-DC7668E1-C846-48C5-A0D5-F4659EC119BB.htm#SUTIL911

From that link: Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

Removing EXCLUDE=grant seems to work, but why would that break the impdp in the first place?

EDIT: My expdp parfile looks like this:

LOGFILE=scott_expdp.log
DUMPFILE=scott.dmp
SCHEMAS=scott
PARALLEL=8
CLUSTER=N
COMPRESSION=ALL
FLASHBACK_TIME=systimestamp
REUSE_DUMPFILES=Y
EXCLUDE=statistics

  • Welcome to DBA.SE and thanks for your participation. Have you tried the obvious? EXCLUDE=GRANT (upper-case). And then a question about your syntax: Is your schema scott a synonym for an existing schema that you don't want to disclose? Or does that schema actually exist? If the schema exists, does that schema hold any objects? Check with select owner, object_name, object_type, status from all_objects where owner = 'scott'; executing as sys or system. – hot2use May 16 at 14:38
  • 1
    @hot2use Yeah I've tried EXCLUDE=GRANT, unfortunately that didn't solve it. Yes, scott is a synonym for the actual schema I'm using. Right now the schema just holds one table. I ran the query and it came up with the one table that I have in the db currently. – Michael May 16 at 16:58
0

This happened because you specified EXCLUDE=GRANT on export as well.

This is expected behavior - according to Oracle.

The workaround is to not specify the same EXCLUDE in expdp and impdp.

See the below (with support access):

DataPump Import (IMPDP) Fails With ORA-39168 (Doc ID 430702.1)

I have just tested this on version 18c and 19c as well, in those newer versions it worked fine without any error.

  • I specified EXCLUDE=statistics in the expdp, so unfortunately I don't think this is the case. I updated the original question with my expdp parfile – Michael May 15 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.