Using External Tables to Move Data

When data file copying is not selected and the data cannot be moved using direct path, the external tables mechanism is used. The external tables mechanism creates an external table that maps to the dump file data for the database table. The SQL engine is then used to move the data. If possible, the APPEND hint is used on import to speed the copying of the data into the database. The representation of data for direct path data and external table data is the same in a dump file. Therefore, Data Pump might use the direct path mechanism at export time, but use external tables when the data is imported into the target database. Similarly, Data Pump might use external tables for the export, but use direct path for the import.

In particular, Data Pump uses external tables in the following situations:

  • Loading and unloading very large tables and partitions in situations where it is advantageous to use parallel SQL capabilities

  • 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

  • Loading a table not created by the import operation (the table exists before the import starts)

Note:

When Data Pump uses external tables as the data access mechanism, it uses the ORACLE_DATAPUMP access driver. However, it is important to understand that the files that Data Pump creates when it uses external tables are not compatible with files created when you manually create an external table using the SQL CREATE TABLE ... ORGANIZATION EXTERNAL statement.

See Also: