Importing data is most efficient when you load only the columns you need into the target database. This becomes apparent when importing unnecessary LOB columns, which can slow down an import significantly. One method of limiting which columns to import is to first only export the columns you need. You can create a temporary table with the abridged column list and export it. Alternatively, create a data pump dumpfile using an external table with only the desired columns. Specify the oracle_datapump access driver in the external table definition.

In the target database, after copying the dumpfile generated on the source, create an external table that references that dumpfile test.dmp.

Sometimes you can't control the contents of dumpfiles (for example, receiving an export performed by a DBA on another project). In this case, omit unneeded columns at import time by importing into a predefined table consisting of a subset of columns.

In versions prior to Oracle 11gr2, if you attempted to import rows into a table with missing columns, you would get the error:

In Oracle 11gr2, the Data Pump utility allows you to import a subset of columns, so you can exclude unnecessary data, like those performance killing LOB columns.

In this example, I exported the full table from the source database with the Data Pump utility.

On the target database, create the same table but with just the columns needed. Here, the document CLOB column is omitted.

On the target database, create the same table but with just the columns needed. Here, the document CLOB column is omitted.

Execute the Data Pump Import, setting parameters table_exists_action to append or truncate, and content=data_only. This prevents Data Pump from replacing your trimmed down table with the source table's full column list.

The import loads just the columns defined in the target table. This will save time and database storage by discarding the unneeded columns.

NOTE: You may encounter a known bug with a misleading error that manifests when importing with mismatched source and target column lists.

If you confirm that XML DB is installed and valid (select comp_id, version, status from dba_registry where comp_id='XDB'), then you are hitting this bug. Per Oracle Support Note ID 1375713.1, the solution is to recompile two packages with the appropriate conditional compiler flags: