When you run an Oracle IMPDP import you expect your dump to load smoothly. Tables, constraints, grants, everything usually flies—until suddenly the import hangs for hours on a specific step.

In many cases, that slowdown happens because your schema contains something sneaky:

👉 Domain Indexes (for example Oracle Text indexes, Spatial indexes, InterMedia, XML DB indexes, custom indextypes, etc.)

If you’ve ever imported a dump and wondered “Why is my impdp stuck building indexes?”, this guide explains exactly why domain indexes slow down an import, what Oracle is doing behind the scenes, and most importantly, how to fix it.

Let’s break it down in simple, human-readable language.

---

What is a Domain Index?

Normal B-Tree indexes are simple—they store sorted column values for quick lookup.

A Domain Index is different. It’s like a smart, application-specific index created by Oracle’s extensible indexing framework. Examples include:

• Oracle Text (CTXCAT, CTXSYS.CONTEXT, CTXRULE)
• Oracle Spatial / Locator (SPATIAL_INDEX)
• XML DB indexes
• Third-party or custom indextypes

These indexes don’t behave like a regular index because:

✔ They require user-defined code
✔ They often use external structures
✔ They must be rebuilt using PL/SQL callbacks
✔ They depend on packages, preferences, and metadata

That’s why they take longer to build and import.

---

Why IMPDP Slows Down When Domain Indexes Exist

1. Impdp Rebuilds Domain Indexes Completely (No Fast Mode)

During import, Oracle must recreate domain indexes from scratch. Unlike a B-Tree index—which is efficiently rebuilt—domain indexes use the indextype’s implementation code.

This means Oracle executes custom logic behind the scenes, such as:

• Tokenizing text
• Generating inverted indexes
• Building spatial trees
• Executing PL/SQL callbacks
• Writing large amounts of metadata

Result: Import waits for these operations to finish.

---

2. These Indexes Are CPU-Heavy and I/O-Heavy

Domain index creation often includes:

• Multiple table scans
• Temporary segment creation
• Parsing large documents (e.g., Oracle Text)
• Generating spatial geometries
• Writing huge LOB segments

So if your database has:

• Large text columns
• Heavy XML columns
• Huge spatial tables
• Many LOBs and metadata structures

then the domain index rebuild step becomes very slow.

---

3. Missing or Wrong Preferences Slow It Even More

Oracle Text example:

If preferences like:

lexer
wordlist
stoplist
storage
filter

are missing or misconfigured on the target database, Oracle will fallback to default behavior and run extremely inefficient rebuild logic.

Your import won’t fail—it will just run painfully slow.

---

4. Statistics Gathering on Domain Indexes Makes It Worse

After building the domain index, Oracle may gather statistics which involves:

• Sampling internal tables
• Scanning text tokens
• Running indextype-specific ODCIStats functions

For large schemas, this alone can add hours to an import.

---

5. Wrong Parameters Used During Import

Many DBAs unknowingly include index rebuild operations during the import phase, which is the most expensive way to handle domain indexes.

If SQLFILE, TRANSFORM, or EXCLUDE options are not tuned, impdp rebuilds everything inline—slowing down the whole import.

---

Clear Signs Your IMPDP Is Slow Due to Domain Indexes

You may notice:

• Import hangs at the phase:
  “Processing object type INDEX”
• Session waits like:
  • CPU
  • db file scattered read
  • lob read
  • direct path write temp
• High temporary tablespace usage
• One worker process at 100% CPU
• Index creation statements in logs like:

CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT

• Active session history showing calls to ODCIIndexCreate() or CTXSYS packages

If you see these symptoms, domain indexes are the culprit.

---

The Solutions — How to Fix Slow IMPDP with Domain Indexes

Let’s go through the best industry-verified solutions.

Solution 1: Exclude Domain Indexes During Import (Fastest Method)

You import everything except the domain indexes, then rebuild indexes manually after the import.

IMPDP command:

impdp user/password \
  directory=DATA_PUMP_DIR \
  dumpfile=export.dmp \
  logfile=import.log \
  EXCLUDE=DOMAIN_INDEX"

or exclude specific index types:

EXCLUDE=DOMAIN_INDEX

Then later run:

ALTER INDEX index_name REBUILD;

or recreate them using the index DDL exported via:

impdp ... SQLFILE=indexes.sql

Why this works:
It allows the import to finish quickly, and you run heavy index builds during a planned maintenance window.

---

Solution 2: Use TRANSFORM=INDEXES:N to Skip Index Creation

Command:

impdp user/password \
  dumpfile=export.dmp \
  directory=DATA_PUMP_DIR \
  logfile=import.log \
  TRANSFORM=INDEXES:N

Then apply index DDL manually.

This skips all indexes, not only domain ones—fastest for large databases.

---

Solution 3: Pre-create Domain Index Preferences & Stoplists

For Oracle Text:

BEGIN
  CTX_DDL.CREATE_PREFERENCE('my_lexer','BASIC_LEXER');
  CTX_DDL.CREATE_STOPLIST('my_stoplist','BASIC_STOPLIST');
END;
/

If preferences are missing before the import, Oracle will silently degrade performance.

---

Solution 4: Disable Statistics Gathering on Import

Large domain indexes suffer heavily during stats gathering.

Use:

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
TRANSFORM=SEGMENT_ATTRIBUTES:N
TRANSFORM=STORAGE:N
TRANSFORM=STATISTICS:N

This prevents slow computation of domain index stats during import.

---

Solution 5: Increase Parallelism (Best for Oracle Text & Spatial)

impdp user/password \
  ... \
  PARALLEL=4

Oracle can sometimes parallelize token builds and LOB reads.
Note: Not all domain indexes support parallel, but Oracle Text & Spatial often do.

---

Solution 6: Rebuild Domain Indexes After Import Using CTX_DDL

For Oracle Text:

ALTER INDEX text_index REBUILD ONLINE;

or

BEGIN
  CTX_DDL.SYNC_INDEX('text_index');
END;
/

---

Solution 7: Temporarily Increase Resources

During index rebuild:

• Add TEMP tablespace
• Set PGA_AGGREGATE_TARGET higher
• Move index tables to faster storage

This reduces I/O stalls during rebuilding.

---

Conclusion — Why Domain Indexes Slow Down IMPDP

To summarize in one line:

👉 Domain Indexes are not simple B-Tree indexes—they require complex logic, heavy CPU, and specialized metadata, making them slow to import.

But the good news is:

• Impdp slowness is predictable
• There are reliable, proven workarounds
• You can speed up imports dramatically by excluding or rebuilding domain indexes correctly

If you apply the solutions above, your imports will run much faster, with fewer surprises during migrations or refresh cycles.

---

Related Articles

• ORA-12514: Listener Does Not Currently Know of Service Requested
• ORA-00257: Archiver Error – Connect Internal Only Until Freed
• ORA-15041: Diskgroup Space Usage Too High – Fix Guide