DataPump Export (EXPDP) Is Hanging While Exporting A Table With LONG Column

Symptoms

DataPump export (expdp) is hanging while exporting a table with LONG column.

Cause

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.

Technical Summary:

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.

Solution

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.

References

BUG:5599947 – DATAPUMP EXPORT VERY SLOW

DataPump Export (EXPDP) Is Hanging While Exporting A Table With LONG Column [ID 813396.1]

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle backup & recovery. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s