Import/export with Oracle datapump

The Oracle data pump is the improved version of imp/exp funtion in Oracle. With the data pump you can move data and metadata from one database to another.

Oracle Data Pump can be used in 2 different ways:
- A command-line function: impdp and expdp.
- Calling it from pl/sql using one of the packages dbms_datapump and dbms_metadata.

The commandline function impdp stands for the Data Pump Import utility.
The commandline function expdp stands for the Data Pump Export utility.
The user interface closely resembles the old imp/exp function, but they are completely separate. Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.

These commandline functions will call the dbms_datapump package.

The new Data Pump Export and Import utilities (invoked with the expdp and impdp commands, respectively) have a similar look and feel to the original Export (exp) and Import (imp) utilities , but they are completely separate. Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.
Oracle recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database 10g features, except for XML schemas. Original Export and Import support the full set of Oracle database release 9.2 features. Also, the design of Data Pump Export and Import results in greatly enhanced data movement performance over the original Export and Import utilities.

When using the datapump you need directory objects in the database. A directory can be created as following (you need a CREATE DIRECTORY privilege to do this):

SQL> CREATE DIRECTORY datapump_directory AS '/tmp/oracle_dp';
SQL> GRANT READ, WRITE ON DIRECTORY datapump_directory TO user1;

Usages of impdp/expdp

Export/Import a complete schema:
expdp username/password@database schemas=USER1 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log
impdp username/password@database schemas=USER1 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log

Export/Import a table:
expdp username/password@database tables=TABLE1,TABLE2 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log
impdp username/password@database tables=TABLE1,TABLE2 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log

Export/Import full database:
expdp username/password@database full=Y directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log
impdp username/password@database full=Y directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log

Export/Import tablespace:
expdp username/password@database tablespaces=TABLSP1 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log
impdp username/password@database tablespaces=TABLSP1 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log

Export/Import transport tablespaces:
expdp username/password@database TRANSPORT_TABLESPACES=TABLSP1 TRANSPORT_FULL_CHECK=Y directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log
impdp username/password@database TRANSPORT_TABLESPACES=TABLSP1 directory=datapump_directory dumpfile=test.dmp logfile=test_dmp.log

Monitoring Job Status

To monitor the data pump jobs you can query the user_datapump_jobs or dba_datapump_jobs table. these views give back all active Data Pump jobs, regardless of their state. They also show all Data Pump master tables not currently associated with an active job. You can use the job information to attach to an active job. Once you are attached to the job, you can stop it, change its parallelism, or monitor its progress. You can use the master table information to restart a stopped job or to remove any master tables that are no longer needed.
With the dba_datapump_sessions view you can see the user sessions that are attached to a job. The information in this view is useful for determining why a stopped operation has not gone away.

SQL> select * from dba_datapump_sessions;

Example:
SQL> select * from user_datapump_jobs;   or
SQL> select * from dba_datapump_jobs;

Oracle recommends that you use the new Data Pump Export and Import utilities instead of the old because the new tools support all new Oracle Database 10g features, except for XML schemas. The exp/imp functions only support Oracle 9.2 features. Also, the design of Data Pump Export and Import are much faster than the old tools.