Oracle Database Utilities

The Oracle Database 11g Release 2 Utilities—comprising Oracle Data Pump and Oracle SQL*Loader—are a set of tools to allow fast and easy data transfer, maintenance, and database administration of Oracle databases.

Oracle Data Pump

Oracle Data Pump is a feature of Oracle Database 11g Release 2 that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise Manager interface.

• Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.
• Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.
• Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.
• Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).

SQL*Loader

SQL*Loader is a high-speed data loading utility that loads data from external files into tables in an Oracle database. SQL*Loader accepts input data in a variety of formats, can perform filtering, and can load data into multiple Oracle database tables during the same load session.

SQL*Loader provides three methods for loading data: Conventional Path Load, Direct Path Load, and External Table Load.

External Tables

A feature has been added to external tables that allows users to preprocess input data before it is sent to the access driver. The ability to manipulate input data with a preprocessor program results in additional loadable data formats, which greatly enhances the flexibility and processing power of external tables.

The types of preprocessor programs that can be used are versatile, ranging from system commands, user-generated binaries (for example, a C program), or user-supplied shell scripts. Because the user supplies the program to preprocess the data, it can be tailored to meet the user’s specific needs. This means that the number of loadable formats is restricted only by the ability to manipulate the original data set.

Technical Information

Oracle Database 11g Release 2

Oracle Data Pump

• Migrating Oracle E-Business Suite to Sun Oracle Database Machine Using Oracle Data Pump (PDF 708KB)
• Oracle Data Pump On Oracle Real Application Clusters (PDF 128KB)
• What's The Diff?—Using the Metadata Differ to Compare Object Metadata and Correct the Differences (PDF 224KB)
• Oracle Data Pump Overview (HTML)
• Oracle Data Pump Technical White Paper (PDF 400KB)
• Oracle Data Pump Encrypted Dump File Support—Oracle White Paper (PDF 188KB)
• Parallel Capabilities of Oracle Data Pump (PDF 132KB)
• Data Transformations With Oracle Data Pump (PDF 164KB)

External Tables

• External Tables: Frequently Asked Questions—FAQ (PDF 152KB)
• Using the Preprocessor Feature with External Tables in Oracle Database 11g—An Oracle White Paper (PDF 152KB)

SQL*Loader

• SQL*Loader Overview (HTML)
• SQL*Loader: Frequently Asked Questions—FAQ (PDF 80KB)

Presentations & Webcasts

Oracle OpenWorld

• Mastering Oracle Data Pump—Dean Gagne (Oracle) & Udi Karni (Kaiser Permanente) (PDF 1.9MB)

Webcasts

• Mastering Oracle Data Pump—Dean Gagne (Oracle) (Webcast 50Minutes)

Discussion Forums

• Database
• Export/Import/SQL Loader & External Tables