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.
