Performing the export
Initial tasks
Create the directory in the file system where you want to place the dump file with the oracle owner, e.g.
oracle@source> mkdir /home/oracle/dump/
With the dba user assign a directory to this path in the source database, e.g.
SQL> CREATE OR REPLACE DIRECTORY dpdump AS '/home/oracle/dump/';
Create the parameter file for the export, assuming schema SCOTT is to be exported:
oracle@source> cd /home/oracle/dump/
oracle@source> vi exp_SCOTT.par
Insert these lines for a small schema
SCHEMAS=scott
DIRECTORY=dpdump
DUMPFILE=db_export_SCOTT_20130412.dmp
LOGFILE=db_export_SCOTT_20130412.log
FLASHBACK_TIME=systimestamp
Insert these lines for a big schema (more than 10GB)
(you can increase the parallel degree dependent of the schema size, see notes below)
SCHEMAS=scott
DIRECTORY=dpdump
DUMPFILE=db_export_SCOTT_20130412%u.dmp
LOGFILE=db_export_SCOTT_20130412.log
FLASHBACK_TIME=systimestamp
COMPRESSION=all
PARALLEL=2
Start export
On OS command line enter:
oracle@source> expdp system@<source database> parfile=exp_SCOTT.par
Password: <system schema password>
The export is now running and it will take some time depending on the schema size. You can follow the export steps on the screen.
Performing the import
Initial tasks
Create the directory in the file system where you want to place the dump file with the oracle owner, e.g.
oracle@target> mkdir /home/oracle/dump/
With the dba user assign a directory to this path in the target database, e.g.
SQL> CREATE OR REPLACE DIRECTORY dpdump AS '/home/oracle/dump/';
Make sure, that default tablespace and temporary tablespace of the exported schema are existing on the target database, otherwise create them. Optionally you can create the user in advance.
Copy the dump files to this directory, e.g.:
oracle@source> scp -p db_export_SCOTT_20130412*.dmp oracle@target:/home/oracle/dump/
Create the parameter file for the import, assuming schema SCOTT is to be exported:
oracle@target> cd /home/oracle/dump/
oracle@target> vi imp_SCOTT.par
Insert these lines for a small schema
SCHEMAS=scott
DIRECTORY=dpdump
DUMPFILE=db_export_SCOTT_20130412.dmp
LOGFILE=db_import_SCOTT_20130412.log
Insert these lines for a big schema
SCHEMAS=scott
DIRECTORY=dpdump
DUMPFILE=db_export_SCOTT_20130412%u.dmp
LOGFILE=db_import_SCOTT_20130412.log
PARALLEL=2
Start import
On OS command line enter:
oracle@linux> imdp system@<target database> parfile=imp_SCOTT.par
Password: <system schema password>
The import is now running and it will take some time depending on the schema size. You can follow the import steps on the screen.
Additional Information
Estimate Storage
Oracle datapump has a feature that estimates the storage needed for the dump file. You can do the export with this command beforehand:
oracle@source> expdp system@<source database> SCHEMAS=scott ESTIMATE_ONLY=yes
Password: <system schema password>
The displayed value is a little generous, when the compression option is used, this value can be devided by 5.
Important Notes
For using the compression option the "Oracle Advanced Compression Option" must be licenced.
When using a Oracle RAC environment and using the parallel option the destination file system for the dump file must be shared, because Oracle could spread the parallel processes over several instances.
Cross-schema references are not exported. The presence of those objects can be detected with this statement:
SQL> SELECT COUNT(1) FROM dba:constraints WHERE constraint_type='R'
AND owner != r_owner AND owner='SCOTT';
Possibly object grants are missing: grants on objects from schemas, that are not migrated to the migrated schema and public grants and synonyms of objects, that are not (yet) migrated.
