impdp slow with TABLE_EXISTS_ACTION=TRUNCATE

impdp slow with TABLE_EXISTS_ACTION=TRUNCATE

the import using impdp and observed that its taking quite long time to complete even after mentioning parallel parameter.

I tried to analyze using various methods and finally with MOS doc id 780784.1, I got to know that the victim is using TABLE_EXISTS_ACTION=TRUNCATE in my impdp command.

Why this happened?

we know that datapump will use two different load methods during import(impdp).

1. Direct load path – this is the main reason why datapump import (impdp) is faster than traditional import (imp)

2. external table path

When the table is already not existing, oracle will use direct path load, but when the table is existing especially IOT, oracle will use external table path only.

I have changed my syntax to TABLE_EXISTS_ACTION=REPLACE and then it got imported fastly.

So, the final observation is if you have any IOT, please either drop it before importing or use TABLE_ExISTS_ACTION=REPLACE

Here itself, I want to list the occasions when oracle will not use direct path.

Usual observation is direct path is always faster than external table path. But datapump cannot use direct path always due to some restrictions and because of this reason, sometimes you may observe impdp run slower than expected.

Now, what are those situations when datapump will not use direct path? If a table exist with

1. A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.

2. A domain index exists for a LOB column.

3. A table is in a cluster.

4. There is an active trigger on a pre-existing table.

5. Fine-grained access control is enabled in insert mode on a pre-existing table.

6. A table contains BFILE columns or columns of opaque types.

7. A referential integrity constraint is present on a pre-existing table.

8. A table contains VARRAY columns with an embedded opaque type.

9. The table has encrypted columns

10. 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

– A referential integrity constraint exists

– A unique index exists

11. Supplemental logging is enabled and the table has at least 1 LOB column.

Note: Data Pump will not load tables with disabled unique indexes. If the data needs to be loaded into the table, the indexes must be either dropped or re-enabled.

12. using TABLE_EXISTS_ACTION=TRUNCATE ON IOT

 Have a HAPPY LEARNING :-)