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 Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump expdp and impdp commands. I have questions regarding this:

  • Can I create a targer schema without a user or should I create a user first (which creates a schema also)?
  • Can I execute expdp and impdp commands using SYS (as sysdba) account? Is that a preferred method?
  • Does this statement take all the objects (data and metadata) from a schema and move these into a different schema? expdp \"/ as sysdba\" schemas=<schemaname> directory=dumpdir dumpfile=<schemaname>.dmp logfile=expdp_<schemaname>.log. So is the target schema an exact copy of the source schema after impdp command?
share|improve this question
up vote 17 down vote accepted

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.

Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE privileges specifically for this type of thing, you'll need to grant access to the Oracle directory object(s) too.)

A full schema export (metadata and contents) would indeed look like:

expdp user/pass schemas=<schemaname> directory=dumpdir \
      dumpfile=<schemaname>.dmp \

If you want to import to a different user/schema (the target database can be the same as the source), you can use:

impdp user/pass schemas=schema1 directory=dumpdir \
      remap_schema=schema1:schema2 \
      dumpfile=schema1.dmp \

If you don't want a complete import, you can set some filters both on data and metadata. See Filtering During Import Operations.

The Utilities Guide has all the details, I strongly recommend reading at least the overview part.

share|improve this answer
I posted a related question. Are you willing to help me with it? Here is the link:… – CodeMed Feb 25 at 23:38
remap_schema was the parameter for impdp I couldn't found anywhere. Thank you! – Alfabravo Apr 28 at 21:03

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.