DataPump Export (EXPDP) Of Partitioned Table Is Very Slow And Apparently Hangs

(Doc ID 752904.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.1 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 09-Dec-2014***

Symptoms

You try to export a partitioned/subpartitioned table (200 partitions, each partition with 4 subpartitions) and expdp seems to hang. You traced the expdp process and observed that for each subpartition the following statement is started:

******************************************************************************
SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ 1
FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUAL

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.00       0.00          0          0          0          0
Fetch        1     5.68      70.26      42616      42673          0          1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3     5.68      70.26      42616      42673          0          1

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=42673 pr=42616 pw=0 time=70266787 us)
1 INDEX FULL SCAN PART_TAB001_UK (cr=42673 pr=42616 pw=0 time=70266777 us)(object id 80566)
1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)
********************************************************************************

If for only one partition the statement needs 70 seconds to complete, then for all 800 subpartitions you can expect 800 x 70 seconds, that means more then 15 hours.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms