How Does Data Pump Access Data?

Data Pump supports two access methods to load and unload table row data: direct path and external tables. Because both methods support the same external data representation, data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the fastest method appropriate for each table.

Data Pump also uses functionality provided in the DBMS_METADATA PL/SQL package to handle all operations involving metadata, including complete extraction, transformation, and re-creation of all database object definitions.

Direct Path Loads and Unloads

The Oracle database has provided direct path unload capability for export operations since Oracle release 7.3 and a direct path API for OCI since Oracle8i. Data Pump technology enhances direct path technology in the following ways:

• Support of a direct path, proprietary format unload.

• Improved performance through elimination of unnecessary conversions. This is possible because the direct path internal stream format is used as the format stored in the Data Pump dump files.

• Support of additional datatypes and transformations.

Data Pump uses direct path load and unload when the structure of a table allows it.

In the following circumstances, Data Pump cannot use direct path loading:

• A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.

• A domain index exists for a LOB column.

• A table is in a cluster.

• A table has an active trigger.

• A table has fine-grained access control enabled in insert mode.

• A table contains BFILE columns or columns of opaque types.

• A referential integrity constraint is present.

• A table contains VARRAY columns with an embedded opaque type.

If any of these conditions exist for a table, Data Pump uses external tables rather than direct path to move the data for that table.

External Tables

The Oracle database has provided an external tables capability since Oracle9i that allows reading of data sources external to the database. As of Oracle Database 10g, the external tables feature also supports writing database data to destinations external to the database. Data Pump provides an external tables access driver (ORACLE_DATAPUMP) that reads and writes files. The format of the files is the same format used with the direct path method. This allows for high-speed loading and unloading of database tables. Data Pump uses external tables as the data access mechanism in the following situations:

• Loading and unloading very large tables and partitions in situations where parallel SQL can be used to advantage

• Loading tables with global or domain indexes defined on them, including partitioned object tables

• Loading tables with active triggers or clustered tables

• Loading and unloading tables with encrypted columns

• Loading tables with fine-grained access control enabled for inserts

• Loading tables that are partitioned differently at load time and unload time