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.

DESC test
Name              NULL?    TYPE
------------------------------------
ID                         NUMBER
COMMENTS                   VARCHAR2(100)
DOCUMENT                   CLOB

--exclude the document CLOB column from the external table
CREATE TABLE test_xt
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY data_pump_dir
      LOCATION ('test.dmp')
    )
    AS SELECT id, comments FROM test;

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

CREATE TABLE test_xt
    ( ID       NUMBER,
      COMMENTS VARCHAR2(100)
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY data_pump_dir
      LOCATION ('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:

ORA-02373: Error parsing INSERT statement FOR TABLE schema.TABLE
ORA-00904: YourMissingColumn: Invalid identifier.

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.

expdp tester tables=test dumpfile=test.dmp

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.

 CREATE TABLE test
  (id       NUMBER,
   comments VARCHAR2(100));

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.

impdp tester tables=test dumpfile=test.dmp \
  table_exists_action=append content=data_only

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.

ORA-38500: Unsupported operation: Oracle XML DB not present.

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:

conn / AS sysdba

ALTER package dbms_metadata_int compile plsql_ccflags = 'ku$xml_enabled:true';

ALTER package dbms_metadata_util compile plsql_ccflags = 'ku$xml_enabled:true';