Skip to Main Content
  • Questions
  • Impdp not failing even if target table have missing column

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 07, 2018 - 9:57 am UTC

Last updated: May 12, 2018 - 1:31 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

My question why import is not failing even the source and target have different table structure
Source DB has below table (with additional column COL3 and populated

SQL> desc tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL VARCHAR2(20)
COL2 VARCHAR2(200)
COL3 VARCHAR2(4000)

SQL> select * from tab1;

COL1 COL2 COL3
----- ----- -----
r1c1 r1c2 r1c3
r2c1 r2c2 r2c3

Target DB
SQL> desc tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL VARCHAR2(20)
COL2 VARCHAR2(200)

I export from Source DB using below EXPDP
expdp CNVABP5/*****@VFICRMC2 DIRECTORY=DPDIR DUMPFILE=coltest.dmp LOGFILE=exp.txt TABLES=TAB1 EXCLUDE=STATISTICS reuse_dumpfiles=y cluster=n

I Import of Target DB using below Impdp
impdp cnvdbo3/****3@CNVABP3 DIRECTORY=DPDIR DUMPFILE=coltest.dmp LOGFILE=imp1.txt REMAP_SCHEMA=CNVABP5:cnvdbo3 TABLE_EXISTS_ACTION=TRUNCATE CONTENT=DATA_ONLY

Import: Release 12.1.0.2.0 - Production on Mon May 7 10:52:47 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "CNVDBO3"."SYS_IMPORT_FULL_17" successfully loaded/unloaded
Starting "CNVDBO3"."SYS_IMPORT_FULL_17": cnvdbo3/********@CNVABP3 DIRECTORY=DPDIR DUMPFILE=coltest.dmp LOGFILE=imp1.txt REMAP_SCHEMA=CNVABP5:cnvdbo3 TABLE_EXISTS_ACTION=TRUNCATE CONTENT=DATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CNVDBO3"."TAB1" 5.937 KB 2 rows
Job "CNVDBO3"."SYS_IMPORT_FULL_17" successfully completed at Mon May 7 10:52:49 2018 elapsed 0 00:00:01

On Target after import below records

SQL> select * from tab1;

COL1 COL2
----- -----
r1c1 r1c2
r2c1 r2c2

and Connor said...

This works in the same way that an insert would also work correctly, ie

SQL> select * from t1;

         A          B          C
---------- ---------- ----------
         1         10        100
         2         20        200
         3         30        300
         4         40        400
         5         50        500

SQL>
SQL> create table t2 ( a int, b int );

Table created.

SQL>
SQL> insert into t2 (a,b)
  2  select a,b from t1;

5 rows created.

SQL>
SQL>
SQL> select * from t2;

         A          B
---------- ----------
         1         10
         2         20
         3         30
         4         40
         5         50


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mayank, May 11, 2018 - 7:29 am UTC

Thanks for reply
But as during Import Impdp, i am coping all data from source to target without any condition , so i think below should be the case

SQL> insert into t2 select * from t1;
insert into t2 select * from t
*
ERROR at line 1:
ORA-00913: too many values

Import should throw error like in old Exp/Imp utility with error (ORA-00904: "COL3": invalid identifier ) , when there is mismatch in source and target table columns

Impdp showing successful when structure is mismatch is misleading and lot of structural error going unhanded in target
Connor McDonald
May 12, 2018 - 1:31 am UTC

Well....impdp is doing its best to obey your wishes. You said:

TABLE_EXISTS_ACTION=TRUNCATE
CONTENT=DATA_ONLY

so we're trying to copy the *data* and not the structure.

REMAP_SCHEMA=CNVABP5:cnvdbo3

Jim, May 11, 2018 - 3:29 pm UTC

I suspect that the remap_schema directive tells the import to only import the columns that match in the destination table.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.