Previous
Previous
 
Next
Next

Choosing the Right Import/Export/Load/Unload Option

The Oracle Database and Oracle Application Express provide several powerful options for importing, exporting, loading, and unloading data. Table: Summary of Oracle Application Express Import/Export Options provides a summary of these options.

Summary of Oracle Application Express Import/Export Options

Feature or Utility Description

Data Load/Unload wizards in Oracle Application Express

  • Easy to use graphical interface

  • Loads/unloads from and to external text files (delimited fields) or XML files

  • Loads/unloads tables only, one table at a time

  • Access only to schema of logged-in user

  • No data filtering

SQL*Loader utility

  • Command-line interface, invoked with sqlldr command

  • Bulk-loads data into the database from external files

  • Supports numerous input formats, including delimited, fixed record, variable record, and stream

  • Loads multiple tables simultaneously

  • Powerful data filtering capabilities

Data Pump Export and Data Pump Import utilities

  • Command-line interface, invoked with expdp and impdp commands

  • Exports and imports from one Oracle database to another (proprietary binary format)

  • Imports/exports all schema object types

  • Imports/exports entire database, entire schema, multiple schemas, multiple tablespaces, or multiple tables

  • Powerful data filtering capabilities

  • High speed

  • Does not support XMLType data

Export and Import utilities

  • Command-line interface, invoked with exp and imp commands

  • Exports and imports from one Oracle database to another (proprietary binary format)

  • Supports XMLType data

  • Does not support the FLOAT and DOUBLE data types

  • Capabilities similar to Data Pump; Data Pump is preferred unless you must import or export XMLType data


Table: Import/Export Scenarios and Recommended Options provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.

Import/Export Scenarios and Recommended Options

Import/Export Scenario Recommended Option

You have fewer than 10 tables to load, the data is in spreadsheets or tab- or comma-delimited text files, and there are no complex data types (such as objects or multivalued fields).

Data Load/Unload wizards in Oracle Application Express

You have to load data that is not delimited. The records are fixed length, and field definitions depend on column positions.

SQL*Loader

You have tab-delimited text data to load, and there are more than 10 tables.

SQL*Loader

You have text data to load, and you want to load only records that meet certain selection criteria (for example, only records for employees in department number 3001).

SQL*Loader

You want to import or export an entire schema from or to another Oracle database. There is no XMLType data in any of the data.

Data Pump Export and Data Pump Import

You want to import or export data from or to another Oracle database. The data contains XMLType data and contains no FLOAT or DOUBLE data types.

Import (imp) and Export (exp)



See Also:

Oracle Database Utilities for more information on Data Pump, the Import and Export utilities, and SQL*Loader