ORA-39165 Schema was not found in export

Question

I'm getting the following error on a data pump export:

ORA-39165: Schema OFDAT was not found.

First I verified that I had full export permission:

GRANT EXPORT FULL DATABASE TO OFDAT;

Then I tried the admin option in case that would do it:

GRANT EXPORT FULL DATABASE TO OFDAT WITH ADMIN OPTION;

I looked at the error more closely in oerr:

[oracle@OptTekOracleDB dpdump]$ oerr ora 39165
39165, 00000, "Schema %s was not found."
// *Cause:  If exporting or importing over the network, either the user
//          specified a schema name that was not found in the source database
//          or else the user lacked the proper EXP_FULL_DATABASE or
//          IMP_FULL_DATABASE role that would allow them to access another
//          schema.  For importing from files, the user specified a schema
//          name not found in the dump file set.
// *Action: Retry the operation using the correct schema name.

Aha! I thought. Even though I have the grant, this is saying I need the role. So I added the role to the user:

GRANT EXP_FULL_DATABASE TO OFDAT;

I am still getting the same error, schema was not found. Can't think of anything else to try.

@phil - here is my expdp parameter file:

DUMPFILE="dev_ofdat_2-1-3-0_afterbackfill.dmp"
LOGFILE="exp_dev_ofdat_2-1-3-0_afterbackfill.log"
DIRECTORY=DATA_PUMP_DIR
COMPRESSION=METADATA_ONLY
CONTENT=ALL
SCHEMAS=('OFDAT')

Very simple. The command line to kick it off is:

expdp ofdat PARFILE=exp_dev_ofdat_2-1-3-0_afterbackfill.dat

Answer 1

When I run into issues like this, I like to simplify the command as much as possible. I would try putting all of the parameters directly in the command line and removing the parameters that are not required. And I would use a dba user to do it. Something like this.

expdp {dbausername}/{dbapassword} SCHEMAS=OFDAT DUMPFILE=dev_ofdat_2-1-3-0_afterbackfill.dmp

Answer 2

If I read the error message correctly, it also states that the dump files you use for import, do not have content for the schema name you specified.

These dump files likely contain content associated with different schema and Oracle (more specifically IMPDP) won't import that content.

When you install the Oracle client software, it comes with a graphical tool 'Oracle SQL Developer'. This tool allows you to export all tables and content from one schema into an SQL file, which in turn can be imported into the schema you want. The Oracle client software is offered for free, but requires that you register on their web site.

Afterwards you can use your standard dump tools again to your hearts content.

This way is only useful if there isn't much data in the original schema, because this can generate a huge file very quickly.

In this file, replace the name of the original schema with the schema name you want and store it. Use the 'Oracle SQL Developer' tool again to execute this file and you are golden.

Answer 3

My observation is that the error

 ORA-39165: Schema XXXX was not found.

could be only a substitute error for other problems. I got his message while using the INCLUDE parameter twice with the same object type

 INCLUDE=TABLE:"IN('T1','T1')"
 INCLUDE=TABLE:"IN('T3','T4')"

After removing one of those lines that schema was "magically" found:)

The same message is also raised if non of the tables listed in the IN list exists (e.g. due to character case issues).