Understanding C$, I$, and E$ Tables in Oracle Data Integrator (ODI)

A practical guide for ODI developers

Oracle Data Integrator (ODI) uses temporary working tables—C$, I$, and E$—as part of its ELT (Extract‑Load‑Transform) processing. These tables often confuse new developers, yet they play a key role in ODI’s architecture. This short guide explains what they are, why ODI creates them, when they are used, and whether you should avoid them.

What Are C$, I$, and E$ Tables?

1. C$ Tables (Command / Staging Extract Tables)

Purpose: Temporary extract tables used when source and staging area differ, or when the LKM requires staging.

Used When:

• Source and target are in different technologies
• No DBLINK exists between source and target
• The chosen LKM loads data through ODI Agent (JDBC), not through DBLINK
• Complex transformations prevent pushing queries to source

What Happens:

ODI extracts data from the source → loads it into C$ in the staging schema → then the IKM consumes it.

2. I$ Tables (Integration / Pre‑Staging Tables)

Purpose: Temporary tables used by IKMs before integrating data into the target.

Used When:

• The IKM needs to perform flow control, uniqueness checks, or incremental logic
• The mapping has constraints or conditions that need evaluation before loading the target

What Happens:

C$ or source data → I$ table → target table after integration steps like inserts, updates, or merges.

3. E$ Tables (Error Tables)

Purpose: Store invalid rows when flow control is enabled.

Used When:

• You enable “FLOW_CONTROL = TRUE” in the IKM
• ODI validates constraints such as Not Null, Uniqueness, or Referential Integrity

What Happens:

When a record fails validation → ODI captures that row in E$ instead of aborting the load.

When Does ODI Create These Tables? (Processing Stages)

Extraction Stage:

• LKM copies data from source into a C$ table

Integration Stage:

• IKM takes data (from C$ or source) → loads into I$ table

Error Handling Stage (Optional):

• If flow control is true → invalid rows go to E$ table

Final Load:

• Valid rows from I$ integrate into the target table
• Temporary C$, I$, and E$ tables are dropped after the session completes

 

Should You Avoid These Tables?

Generally: YES, if performance matters.

ODI can perform much faster when you avoid C$/I$/E$ tables—because fewer temp tables mean fewer I/O operations and less staging overhead.
But avoidance must be done carefully.

How to Avoid Them

1. Avoid C$ Tables

Use DBLINK‑based LKMs so that ODI can push SQL directly:

• LKM Oracle to Oracle (DBLINK)
• LKM Oracle to Oracle Pull (DBLINK)

Also:

• Set the staging area = SOURCE
• Ensure Instance / dblink is filled in ODI topology
• Keep transformations simple so they can be pushed down

2. Avoid I$ Tables

Disable unnecessary flow control:

• Set FLOW_CONTROL = FALSE
• Use IKM Oracle Control Append or similar fast-load IKMs

3. Avoid E$ Tables

E$ tables only appear if flow control is enabled.

To avoid them:

• Set FLOW_CONTROL = FALSE
• Let the database enforce constraints instead of ODI

When You Should NOT Avoid These Tables

You should keep C$/I$/E$ tables when:

• Source and target are different technologies (File → Oracle, SQL Server → Oracle)
• You require constraint validation before loading
• Large transformations cannot run in source DB
• You must capture detailed error rows

In these cases, ODI’s temporary tables ensure correctness and reliability.

Summary Cheat Sheet

Table	Purpose	Created By	Avoidable?
C$	Extract staging from source	LKM	✔️ Yes, with DBLINK + staging on source
I$	Pre‑integration staging	IKM	✔️ Yes, by disabling flow control
E$	Error rows	IKM (flow control)	✔️ Yes, turn off flow control