Column count in a export and import from source to destination

Question

Can anyone help me with the below query. Do no of column should match in source and target table while export and import data from source to destination using datapump in Oracle 11.1

Eg: we are exporting sourcedb.tab(10 columns) and importing to targetdb.tab(11 columns).

Will this work or will give an error.

Answer 1

It won't work, as far as I can tell. Target table has to match the source table.

So, what can you do?

• create a database link between those two databases and insert rows manually, e.g.

  insert into target@db_link (col1, col2, ..., col10, col11)
    select col1, col2, ..., col10, null
    from source
  

• drop 11th column from the target table, perform import and then alter table to re-create the 11th column

Answer 2

This should work but I haven't tried.

From Oracle 11.2 documentation (Can't find that for 11.1, but most likely the same):

When Data Pump detects that the source table and target table do not match (the two tables do not have the same number of columns or the target table has a column name that is not present in the source table), it compares column names between the two tables. If the tables have at least one column in common, then the data for the common columns is imported into the table (assuming the datatypes are compatible). The following restrictions apply:

This behavior is not > supported for network imports.

The following types of columns cannot be dropped: object columns, object attributes, nested table columns, and ref columns based on a primary key.

Also note that you need to set parameter TABLE_EXISTS_ACTION=APPEND (or TRUNCATE , which remove all existing data). Otherwise, data pump will take the default value of SKIP leaving the table as is.

11.2 Documentation of Data Pump Import