DataPump export (expdp) is hanging while exporting a table with LONG column.
The default method that DataPump uses for loading and unloading data is direct path, when the structure of a table allows it. Note that if the table has any columns of datatype LONG, then direct path must be used.
In specific situations in which direct path cannot be used for loading and unloading the data, Data Pump uses external tables method rather than direct path to load/unload the data for that table. This slows down the export process and in some situations the export completely hangs.
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 loader 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. The default method that Data Pump uses for loading and unloading data is direct path, when the structure of a table allows it. Note that if the table has any columns of datatype LONG, then direct path must be used.
The following sections describe situations in which direct path cannot be used for loading and unloading.
Situations in Which Direct Path Load Is Not Used
If any of the following conditions exist for a table, Data Pump uses external tables rather than direct path to load the data for that table:
— 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.
— There is an active trigger on a pre-existing table.
— Fine-grained access control is enabled in insert mode on a pre-existing table.
— A table contains BFILE columns or columns of opaque types.
— A referential integrity constraint is present on a pre-existing table.
— A table contains VARRAY columns with an embedded opaque type.
— The table has encrypted columns
— The table into which data is being imported is a pre-existing table and at least one of the following conditions exists:
* There is an active trigger
* The table is partitioned
* fgac is in insert mode
* A referential integrity constraint exists
* A unique index exists
* Supplemental logging is enabled and the table has at least 1 LOB column.
Situations in Which Direct Path Unload Is Not Used
If any of the following conditions exist for a table, Data Pump uses the external table method to unload data, rather than direct path:
— Fine-grained access control for SELECT is enabled.
— The table is a queue table.
— The table contains one or more columns of type BFILE or opaque, or an object type containing opaque columns.
— The table contains encrypted columns.
— A column of an evolved type that needs upgrading.
— A column of type LONG or LONG RAW that is not last.
Note that if the table has any columns of datatype LONG, then direct path must be used, not external table method.
Use the parameter ACCESS_METHOD=DIRECT_PATH during datapump export/import.
BUG:5599947 – DATAPUMP EXPORT VERY SLOW
DataPump Export (EXPDP) Is Hanging While Exporting A Table With LONG Column [ID 813396.1]