Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
What's the exact command-line you're using? –  Phil May 10 '12 at 18:00
    
Hmm, odd. Can you give it a go with SCHEMAS=OFDAT instead of with brackets and quotes? –  Phil May 11 '12 at 8:16

1 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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