Data Pump Export and Import - Step by Step

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 'Z:\output_dir';

– “Z:\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.