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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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


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:


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:


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
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
Please refer to: Materialized Views Created in Version 8 are Not Exported Using Datapump Or Traditional Export (Doc ID 1602534.1) – user50125 Oct 11 '14 at 20:49

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

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.

share|improve this answer

Your Answer


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.