Choosing the Right Import/Export/Load/Unload Option

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

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 a number of 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`)