Oracle Data Pump is a new and unique feature of Oracle Database 11g Release 2. A new public interface package, DBMS_DATAPUMP, provides a server-side infrastructure for fast data and metadata movement between Oracle databases. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.
Data Pump automatically manages multiple, parallel streams of unload and load for maximum throughput. The degree of parallelism can be adjusted on-the-fly. There are new and easy-to-use Export and Import utilities (expdp and impdp), as well as a web-based Enterprise Manager export/import interface.
Data Pump is an integral feature of Oracle Database 11g Release 2 and is available in all configurations. However, parallelism is available only in Enterprise Edition.
Operations performed with the new Data Pump Export and Import utilities are typically much faster than operations performed with the original Export and Import utilities. With Data Pump Export, when the direct path method of unloading is used, a single stream of data unload is about two times faster than original Export. This is because the direct path API has been modified to be even more efficient than before. Depending on the level of parallelism, the performance improvement can be even greater.
With Data Pump Import, a single stream of data load is about 15-45 times faster than original Import. This is because original Import uses only conventional mode inserts, whereas Data Pump Import uses the direct path method of loading. As with Export, the performance improvement can be even greater depending on the level of parallelism.
No database tuning is required to achieve maximum Data Pump performance. The initialization parameters should be sufficient out of the box.
Improved Management Restart
Every Data Pump operation has a master table that is created in the schema of the user running a Data Pump job. The master table maintains information about all aspects of the job, such as the current state of every object exported or imported and its location in the dump file set. In the event of a planned or unplanned job stoppage, Data Pump knows which objects were currently being worked on and whether or not they completed successfully. Therefore, all stopped Data Pump jobs can be restarted without loss of data as long as the master table and dump file set remain undisturbed while the job is stopped.
Fine-Grained Object Selection
A Data Pump job can exclude or include virtually any type of object and any subset of objects within a type. The following client parameters are used:
The EXCLUDE parameter filters the metadata that is exported and imported by specifying objects and object types to be excluded from the current operation. An optional name qualifier can be used for finer selectivity within each object type specified.
The INCLUDE parameter filters the metadata that is exported and imported by specifying objects and object types to be included for the current operation. An optional name qualifier can be used for finer selectivity within each object type specified.
The CONTENT parameter specifies what is exported or imported: metadata only, data only, or both.
The QUERY parameter filters data by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table.
Monitoring and Estimating Capability
In addition to the standard progress and error messages printed by the client and to the log file, the new interactive STATUS command displays cumulative status of the job, along with a description of the current operation. An estimated completion percentage for the job is also returned. The user can also specify a time period, in seconds, for an automatic status update at specific intervals.
Because more than one client can be attached to a running job, the user can start a job at work, detach from it, go home, reattach to the job at home, and monitor it throughout the evening.
The start of every export job now includes an estimate phase in which the approximate amount of all data to be unloaded is determined. This allows the user to allocate a sufficient amount of disk space for the dump file set.
Data Pump Export and Import both support a network mode in which the job's source is a remote Oracle instance. When you perform an import over the network, there are no dump files involved because the source is another database, not a dump file set.
When you perform an export over the network, the source can be a read-only database on another system. Dump files are written out on the local system just as they are with a local (non-networked) export.
In Oracle Database 11g Release 2, Oracle Data Pump provides a server-side infrastructure and new high-speed, parallel Export and Import utilities for highly efficient bulk data and metadata movement between databases. Database administrators can now move data and metadata between databases faster and easier than ever before.