Step by Step Tutorial on How to Use Oracle Data Pump Export and Import

smallwei 12:56 pm on October 14, 2009 | Answers: 1    
Tags: export (5), import (23), oracle (136), oracle data, step (3), summary (3), tutorial (5)

Rate Knowledge: (1 votes)

 Loading ...

• smallwei 1:37 pm on October 14, 2009
  

  Setup both your source and target database:
  1. sqlplus to source and target DB as sys or system.

  2. create directory with the following command:

  CREATE DIRECTORY dpump_dir1 AS 'D:\output_dir';

  – “D:\output_dir” can be any directory where you want the exported dump file to go.

  3. Exit sqlplus.

  4. Run the following command to export using data pump:

  expdp system/[PASSWORD]@[SID] schemas=[SCHEMA] DIRECTORY=dpump_dir1 JOB_NAME=hr DUMPFILE=[SCHEMA]_[SID]_%u.dmp PARALLEL=4

  – replace strings in [ ] with the ones for your environment.

  5. Run the following command to import using data pump:

  impdp system/[PASSWORD]@[SID] schemas=[SCHEMA] DIRECTORY=dpump_dir1 JOB_NAME=hr DUMPFILE=[SCHEMA]_[SID]_%u.dmp PARALLEL=8

  – replace strings in [ ] with the ones for your environment.

  Notes:
  - If your target database is on another host, be aware of the directory setup.
  - If you want to import into existing table, simply add TABLE_EXISTS_ACTION=APPEND as part of the command.
  - If you only want to import certain tables, use TABLES=[TABLE_NAME1,TABLE_NAME2 ...etc].
  - You can also use TABLE_EXISTS_ACTION=TRUNCATE to first truncate the target table before the import.
  - You can adjust PARALLEL parameter depending on the number of CPUs on your system.