logo for asktheoracle.net
Home
AskTheOracle Blog
Oracle Tips & Tricks
Oracle Training
Oracle Tutorials
PL/SQL
SQL
Advanced Tutorials
Performance Tuning
Certification
Oracle 10g
Oracle 11g
Oracle and .Net
Oracle Utilities
Developer Tools
Oracle Questions?
Oracle News
Search This Site
About Us
Disclaimer
Privacy Policy
Contact Us
leftimage for asktheoracle.net

A Guide To Oracle Data Pump

Oracle Data Pump is a server-side utility that replaces the venerable export and import utilities. The export and import utilities have been provided by Oracle for many years as a way of making logical backups of the database and of copying data from one database to another and Oracle data pump does exactly the same thing.

So why were they replaced?
Well, one of the biggest problems with the old export and import utilities is that there was no security.  These tools ran on the client and the export dump file was stored on the  client and was therefore accessible to anyone who could log on to the pc.

As Data Pump runs on the server the export dump files can be stored only in directory objects
created by the dba and the dba  therefore controls who has access to the dump files.  Oracle data pump jobs also run asynchronously which enables you to start a job (to export or import data), disconnect and then re-connect later to monitor progress

How does Oracle Data Pump work?

Oracle Data Pump consists of 3 distinct components:
  • expdp and impdp (the command line interface tools)
  • the PL/SQL package DBMS_DATAPUMP (the data pump api)
  • the PL/SQL package DBMS_METADATA (the meta data api)
Expdp and impdp are used to export data from and import data to the database respectively by invoking sub routines in DBMS_DATAPUMP. Meta data is extracted and loaded by use of the DBMS_METADATA package.

As with the export and import utilities data pump can work on several different levels: (full) database, tablespace, transportable tablespace, user (schema) or table levels. Unlike export/import, however, data pump can use several different methods to export or import data.

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.
These methods are:
  • data file copying
  • direct path
  • external tables
  • conventional path
  • network link

Data file copying

The quickest way to copy data from one database to another is just to transfer the meta data (the information about the structure of the database such as tables, partitions, sub partitions) via use of Oracle  data pump and then physically copy the relevant data files via use of operating system commands. This method  is most commonly used for transferring a whole tablespace (when the TRANSPORT_TABLESPACES parameter is specified) but can also be used at the table level when TRANSPORTABLE=ALWAYS is specified. The character set must be the same on both databases to be able to use this method.

Direct path transfer

This is the second fastest method of transferring data as it bypasses the SQL layer of the database and is used automatically by data pump when the structure of the tables being exported imported allow it. There are different restrictions for using this method on export and import - the full set of conditions is available in the Oracle utilities documentation.

External tables

When direct path transfer can't be used the next best method is to use the external table mechanism which is similar to but not the same as the SQL*Loader external table mechanism. When using this method the dump file is regarded as a table (just like any other except that it is outside the Oracle database) and the database SQL engine is then used to import or export the relevant data.  Oracle data pump will select this method automatically if it can't use direct path load or unload and data may be loaded using the external table mechanism having been unloaded using direct path and vice versa.

Conventional path

The conventional path is used when none of the other methods can be used for loading/unloading data because of the way the tables have been built.  More work has to be done by the database to load or unload data this way so performance is generally slower with this method.

Network link

The data pump import and export utilities can also use a database link to load or unload data from a remote database. However this is the slowest method as all the data has to be transferred over the network and is not recommended for large volumes of data. This method has to be used for read only databases.

Looking for high quality Oracle training? We offer Oracle training in the UK and New Zealand for both developers and dbas. Contact us today and discover how we can help you slash costs and explode productivity.

Streamline your Oracle systems with expert Oracle consulting services from Smartsoft. With skills honed to a fine edge over many years with myriad clients in a variety of industries, our consultants have the knowledge and experience to help you. Click here for a consultation application form.