Skip to content

How to Resolve ORA-39165: Schema was not found

  • Oracle

ORA-39165

Tried to perform data migration by data pump, but it failed with ORA-39165.

[oracle@test ~]$ impdp system@orclpdb schemas=BAR remap_schema=FOO:BAR dumpfile=export_foo.dmp logfile=import_bar.log
...
ORA-39002: invalid operation
ORA-39165: Schema BAR was not found.

ORA-39165 means that the data in dump files you want to import does not contain the schema you specified in the SCHEMAS parameter.

Solutions

When importing data, the data source is the dump files, which may contain some other schemas, but not the schema you specified. You had better inspecting the content of dump files, the exporting log may provide some helps.

REMAP_SCHEMA

The case we mentioned in the beginning is a little special, which involved REMAP_SCHEMA parameter. Let's see its format.

REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA>

In this case, we tried to import the target schema specified in SCHEMAS, which should be remapped from the source schema. But in fact, there's no content for the target schema in the dump file.

To solve this, we have 2 choices.

  • Remove SCHEMAS parameter from the command line. That is, we import all data in the dump file without specifying any schema, if it contains only the source schema.
  • Specify the source schema instead of the target schema in SCHEMAS parameter. This makes the command clear and specific.

Here we apply the second choice.

[oracle@test ~]$ impdp system@orclpdb schemas=FOO remap_schema=FOO:BAR dumpfile=export_foo.dmp logfile=import_bar.log

After that, REMAP_SCHEMA will work for us.

Leave a Reply

Your email address will not be published. Required fields are marked *