DataPump Export/Import Of LOBs Are Not Executed in Parallel

Applies to:

Symptoms

You try to export/import a table with a LOB column using DataPump with PARALLEL > 1 and observed that the parallelism is not used.

Cause

 DataPump does not export or import table data in parallel, if the table contains BasicFiles LOB columns. This was a restriction of BasicFiles LOBs.
The restriction is also seen in 11gR2 where the export of big table with clob (as SecureFile) is not done parallel. The confirmation is received from DEV in - EXPORT OF CLOB AD SECUREFILE NOT DONE IN PARALLEL, closed as not a bug due to: exporting a table with LOBs is
still something that Data Pump does not allow to use parallel DML (PDML). It is being worked at and looking to fix it in 12.2 release. The unpublished documentation Bug 21256503 : DATAPUMP CAN'T USE PARALLEL DML FOR SECUREFILE LOBS, was created in order to correct 11gR2 Utilities
documentation with this information.

Solution

Oracle recommends to migrate to the new LOB storage format of SecureFiles while working in 11gR1 onwards as internal tests made clear that a huge decrease in time could be achieved by transform of BasicFiles into SecureFiles.
The result of the internal tests can be found in Note 1595380.1 - DataPump Export (EXPDP) Is Taking  Long time To Export Tables With BASICFILES.

Please note, a SecureFile can only be created in an automatic segment space management (ASSM) tablespace.

For more information, please refer to:

Note 1490228.1 Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs)

While working in 10.2.x versions, or with BasicFiles LOBS, there are several workarounds which could be used to try to speed up the data transfer:

1. If the table is partitioned, then there would be <n> DataPump Worker processes active (where <n> is the value of the parallel parameter), each unloading data from one of the table partitions.

2. Include a where clause in the EXPDP command to segment the export and run multiple EXPDP to simulate parallelism:

The expdp command has a QUERY parameter which enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement.
The query_clause is typically a WHERE clause for fine-grained row selection, but could be any SQL clause
This QUERY parameter could be used to run different exports from the same table at the same time, exporting a range of rows in each operation.

For example:

- First export could be run using QUERY parameter:

- Second export could be run using QUERY parameter:

- Third export could be run using QUERY parameter:

and so on.

Please note that DataPump can acquire exclusive locks and can get stuck if it cannot get it. For DataPump Import you may want to specify DATA_OPTIONS=DISABLE_APPEND_HINT, which can help with the locking issues on import.

Please refer to:

Note 462365.1 - DataPump Export (expdp) Or Import (impdp) Hangs On Lock

3. Excluding the use of DataPump, create table as select with parallel hint using a DBLINK to the target DB:

In the target DB, create a table as select using a dblink to the table in the source database. This query should include the parallel hint.

For example:

create table table2 (......) as select /*+ parallel (table1, 4) */ * from table1@dblink to source DB;

In this case you would need to keep in mind restrictions like the LONG RAW field, which raises error:

when queried through dblink.

References