ORA-39165 Schema was not found in export

datapumporacleoracle-11g-r2

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

Best Answer

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
Related Question