How to transfer data using expdp and impdp commands?

Question

I'm an 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's expdp and impdp commands. I have questions regarding this:

• Can I create a target 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?

Answer 1

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 \
      logfile=expdp_<schemaname>.log

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 \
      logfile=impdp_schema2.log

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.

Answer 2

For importing the truncated tables, i.e you only want the data to be imported back to the table:

impdp user/pass TABLES=dept DIRECTORY=TEST_dir dumpfile=TEST.dmp logfile=expdpTEST.log TABLE_EXISTS_ACTION=REPLACE

Above my table is dept which I want to import. I have truncated it earlier. From the dumpfile which is TEST.dmp, and logfile which is expdpTEST.log I want only the data to be imported (the table structure will be the same so we use the parameter TABLE_EXISTS_ACTION).

If you have truncated 2 tables, for example emp and dept, and emp table has dept_id as the foreign key, then you need to import the dept table first and then the emp table to avoid errors during import.

more info http://satya-dba.blogspot.in/2009/05/datapump.html

Answer 3

expdp \"/ as sysdba\" 
schemas=<schemaname> directory=dumpdir dumpfile=<schemaname>.dmp logfile=expdp_<schemaname>.log